Automate Company Data Enrichment

function onOpen() {
const ui = SpreadsheetApp.getUi();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ensureHeaders(sheet); // Ensure headers are set
setupStatusField(sheet); // Set up the status field

// Count and display initial statistics
countAndDisplayCurrentStats(sheet);

ui.createMenu('Company Enrichment')
.addItem('Enrich All Companies', 'runEnrichmentAllCompanies')
.addItem('Enrich New/Failed Companies', 'runEnrichmentNewCompanies')
.addItem('Choose Row Range to Enrich', 'chooseRowRangeToEnrich')
.addItem('Set Max Row for All/New Enrichment', 'setMaxRowForEnrichment')
.addToUi();
}

function chooseRowRangeToEnrich() {
const ui = SpreadsheetApp.getUi();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get the total number of rows with data
const lastRow = sheet.getLastRow();

// Show input dialog for starting row
const startResponse = ui.prompt(
'Choose Row Range - Step 1',
`Enter the STARTING row number (Row 3 to ${lastRow}):\n\nNote: Row 1 contains status info, Row 2 contains headers, so data rows start from Row 3.`,
ui.ButtonSet.OK_CANCEL
);

// Check if user clicked OK and entered a value
if (startResponse.getSelectedButton() !== ui.Button.OK) {
return; // User cancelled
}

const startInputText = startResponse.getResponseText().trim();
const startRowNumber = parseInt(startInputText);

// Validate the start row input
if (isNaN(startRowNumber)) {
ui.alert('Invalid Input', 'Please enter a valid number for the starting row.', ui.ButtonSet.OK);
return;
}

if (startRowNumber < 3) {
ui.alert('Invalid Row', 'Please enter a starting row number 3 or higher (rows 1-2 are reserved for headers).', ui.ButtonSet.OK);
return;
}

if (startRowNumber > lastRow) {
ui.alert('Invalid Row', `Please enter a starting row number between 3 and ${lastRow}.`, ui.ButtonSet.OK);
return;
}

// Show input dialog for ending row
const endResponse = ui.prompt(
'Choose Row Range - Step 2',
`Enter the ENDING row number (${startRowNumber} to ${lastRow}):\n\nStarting row: ${startRowNumber}\nEnter the last row you want to enrich (inclusive).`,
ui.ButtonSet.OK_CANCEL
);

// Check if user clicked OK and entered a value
if (endResponse.getSelectedButton() !== ui.Button.OK) {
return; // User cancelled
}

const endInputText = endResponse.getResponseText().trim();
const endRowNumber = parseInt(endInputText);

// Validate the end row input
if (isNaN(endRowNumber)) {
ui.alert('Invalid Input', 'Please enter a valid number for the ending row.', ui.ButtonSet.OK);
return;
}

if (endRowNumber < startRowNumber) {
ui.alert('Invalid Range', `The ending row (${endRowNumber}) must be greater than or equal to the starting row (${startRowNumber}).`, ui.ButtonSet.OK);
return;
}

if (endRowNumber > lastRow) {
ui.alert('Invalid Row', `Please enter an ending row number between ${startRowNumber} and ${lastRow}.`, ui.ButtonSet.OK);
return;
}

// Confirm the action
const confirmResponse = ui.alert(
'Confirm Range Enrichment',
`This will enrich companies from row ${startRowNumber} to row ${endRowNumber} (${endRowNumber - startRowNumber + 1} rows). Continue?`,
ui.ButtonSet.YES_NO
);

if (confirmResponse === ui.Button.YES) {
updateEnrichmentStatus(sheet, 'In Progress...');
countAndDisplayCurrentStats(sheet);
runEnrichmentFromRowRange(startRowNumber, endRowNumber);
}
}

// [Note: This is a large script - showing key functions for brevity]
// The complete script includes all helper functions for:
// - Setting up headers and status fields
// - Processing companies in batches
// - Handling API responses and errors
// - Updating statistics and status