1st Step: Data Dump: Read Through the Code and Then Steps to Set it Up

// --- CONFIGURATION ---
const CONFIG = {
  CALLRAIL_ACCOUNT_ID: '628189884',          // Your CallRail Account ID
  CALLRAIL_API_KEY: '8e82a8a77a8ffee2612f21008c921b7f', // Your CallRail API Key
  CALLRAIL_COMPANY_ID: '665808319',          // Your specific Company ID
  
  SHEET_NAME_CALLS: 'Calls',
  SHEET_NAME_FORMS: 'Forms',

  // IMPORTANT: For de-duplication, CallRail's 'id' field is used as the unique identifier.
  UNIQUE_ID_FIELD: 'id', 

  // --- FIELDS FOR CALLS ---
  // Only these specific fields will be fetched for calls.
  CALL_FIELDS: 'id,start_time,customer_phone_number,source', // 'id' must be included for de-duplication
  CALL_DATE_FIELD: 'start_time', // Field to use for sorting and date reference on sheet

  // --- FIELDS FOR FORMS ---
  // Only these specific fields will be fetched for forms.
  FORM_FIELDS: 'id,source,customer_phone_number,submitted_at', // 'id' must be included for de-duplication
  FORM_DATE_FIELD: 'submitted_at', // Field to use for sorting and date reference on sheet

  // Time zone for triggers and date calculations (e.g., 'America/New_York', 'America/Los_Angeles')
  SCRIPT_TIME_ZONE: 'America/New_York', 

  // --- NEW: Data Retention ---
  MONTHS_TO_KEEP_DATA: 3, // Data older than this many months will be deleted from the sheet
};

// --- CORE UTILITY FUNCTIONS ---

/**
 * Calculates the start and end dates for the previous month.
 * Dates are formatted as YYYY-MM-DD.
 * This is primarily for logging and UI messages to indicate which period 'last_month' refers to.
 * @returns {{startDate: string, endDate: string}} An object with start and end dates.
 */
function getLastMonthDateRange() {
  const now = new Date();
  const year = now.getFullYear();
  const month = now.getMonth(); // 0-indexed: Jan=0, Dec=11

  const firstDayOfCurrentMonth = new Date(year, month, 1);
  const lastDayOfLastMonth = new Date(firstDayOfCurrentMonth);
  lastDayOfLastMonth.setDate(firstDayOfCurrentMonth.getDate() - 1);
  const firstDayOfLastMonth = new Date(lastDayOfLastMonth.getFullYear(), lastDayOfLastMonth.getMonth(), 1);

  const formatDate = (date) => Utilities.formatDate(date, 'GMT', 'yyyy-MM-dd'); // Using GMT for consistent formatting

  return {
    startDate: formatDate(firstDayOfLastMonth),
    endDate: formatDate(lastDayOfLastMonth)
  };
}


/**
 * Fetches data from the CallRail API with pagination handling.
 * This version uses CallRail's 'date_range' keywords like 'last_month'.
 * @param {string} endpoint - The API endpoint (e.g., 'calls.json', 'form_submissions.json').
 * @param {string} fields - Comma-separated list of fields to retrieve.
 * @returns {Array<Object>} An array of data objects.
 */
function fetchCallRailData(endpoint, fields) {
  const allData = [];
  let page = 1;
  let totalPages = 1;

  // Use CallRail's specific keyword for last month
  const callrailDateRangeKeyword = 'last_month'; 
  Logger.log(`Fetching data from CallRail endpoint: ${endpoint} for date range: '${callrailDateRangeKeyword}' keyword.`);

  do {
    let url = `https://api.callrail.com/v3/a/${CONFIG.CALLRAIL_ACCOUNT_ID}/${endpoint}?fields=${fields}&date_range=${callrailDateRangeKeyword}&page=${page}`;
    
    // Add company_id filter if specified in config
    if (CONFIG.CALLRAIL_COMPANY_ID) {
      url += `&company_id=${CONFIG.CALLRAIL_COMPANY_ID}`;
    }

    try {
      const response = UrlFetchApp.fetch(url, {
        method: 'get',
        headers: {
          'Authorization': `Token token="${CONFIG.CALLRAIL_API_KEY}"`
        },
        muteHttpExceptions: true // To get error details in response.getContentText()
      });

      const responseCode = response.getResponseCode();
      const responseText = response.getContentText();

      if (responseCode === 200) {
        const jsonData = JSON.parse(responseText);
        
        const dataKey = endpoint.split('.')[0]; // 'calls' or 'form_submissions'
        const currentData = jsonData[dataKey];

        if (currentData && currentData.length > 0) {
          allData.push(...currentData);
        }
        
        totalPages = jsonData.total_pages || 1; 
        page++;
      } else {
        Logger.log(`Error fetching CallRail data from ${url}: HTTP ${responseCode} - ${responseText}`);
        SpreadsheetApp.getUi().alert('CallRail API Error', `Failed to fetch data from CallRail. HTTP ${responseCode} - ${responseText}`, SpreadsheetApp.getUi().ButtonSet.OK);
        break; // Exit loop on error
      }
    } catch (e) {
      Logger.log(`Exception when fetching CallRail data: ${e.message}`);
      SpreadsheetApp.getUi().alert('Apps Script Error', `An error occurred while fetching CallRail data: ${e.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
      break; // Exit loop on exception
    }
  } while (page <= totalPages);

  return allData;
}

/**
 * Reads existing data from a sheet to facilitate de-duplication and updates.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - The target sheet.
 * @param {string[]} headers - The headers of the sheet.
 * @param {string} uniqueIdField - The field name used as a unique identifier (e.g., 'id').
 * @returns {Map<string, number>} A map of uniqueId -> rowIndex.
 */
function getExistingSheetIdMap(sheet, headers, uniqueIdField) {
  const idMap = new Map();
  if (sheet.getLastRow() < 2) return idMap; // No data rows, only headers

  const uniqueIdColIndex = headers.indexOf(uniqueIdField);
  if (uniqueIdColIndex === -1) {
    Logger.log(`Unique ID field '${uniqueIdField}' not found in sheet headers.`);
    return idMap;
  }

  const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, headers.length);
  const values = dataRange.getValues();

  values.forEach((row, index) => {
    const id = row[uniqueIdColIndex];
    if (id) {
      idMap.set(String(id), index + 2); // Store row index (1-based)
    }
  });
  return idMap;
}

/**
 * Processes new data, updating existing rows and appending new ones, then sorts the sheet.
 * @param {string} sheetName - The name of the sheet to write to.
 * @param {Array<Object>} newData - The array of new data objects from API.
 * @param {string} uniqueIdField - The field name used as a unique identifier.
 * @param {string} dateField - The field name used for date, to sort the sheet.
 */
function processAndWriteData(sheetName, newData, uniqueIdField, dateField) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    Logger.log(`Created new sheet: ${sheetName}`);
  }

  // Determine headers based on the requested fields
  const desiredHeaders = (sheetName === CONFIG.SHEET_NAME_CALLS) ? 
                         CONFIG.CALL_FIELDS.split(',') : 
                         CONFIG.FORM_FIELDS.split(',');

  // Ensure headers are correct. If not, clear and set new headers.
  const existingHeaders = sheet.getDataRange().getValues()[0] || [];
  if (existingHeaders.join(',') !== desiredHeaders.join(',')) {
    sheet.clearContents();
    sheet.clearFormats();
    sheet.appendRow(desiredHeaders);
    Logger.log(`Updated headers for sheet: ${sheetName}`);
  }
  
  if (newData.length === 0) {
    // If no new data, and sheet only has headers, add a message.
    if (sheet.getLastRow() === 1) {
      sheet.appendRow(['No data found for the selected period or company.']);
    }
    Logger.log(`No new data to write to sheet: ${sheetName}`);
    return;
  }

  const headers = desiredHeaders; // Use desired headers as the reference for processing
  const uniqueIdColIndex = headers.indexOf(uniqueIdField);
  const dateColIndex = headers.indexOf(dateField);

  if (uniqueIdColIndex === -1) {
    throw new Error(`Unique ID field '${uniqueIdField}' not found in desired headers for sheet '${sheetName}'.`);
  }
  if (dateColIndex === -1) {
    Logger.log(`Warning: Date field '${dateField}' not found in desired headers for sheet '${sheetName}'. Sheet will not be sorted by date.`);
  }

  const idMap = getExistingSheetIdMap(sheet, headers, uniqueIdField);
  const updates = new Map(); // Map of rowIndex -> rowData (for batch update)
  const appends = [];        // Array of rowData (for batch append)

  newData.forEach(record => {
    const recordId = String(record[uniqueIdField]);
    const rowValues = headers.map(header => {
      const value = record[header];
      // Handle potential nested objects or arrays by stringifying them
      if (typeof value === 'object' && value !== null) {
        return JSON.stringify(value);
      }
      return value;
    });

    if (idMap.has(recordId)) {
      // Existing record: mark for update
      const rowIndex = idMap.get(recordId);
      updates.set(rowIndex, rowValues);
    } else {
      // New record: mark for append
      appends.push(rowValues);
    }
  });

  // Perform batch updates
  if (updates.size > 0) {
    updates.forEach((rowValues, rowIndex) => {
      sheet.getRange(rowIndex, 1, 1, rowValues.length).setValues([rowValues]);
    });
    Logger.log(`Updated ${updates.size} rows in sheet: ${sheetName}`);
  }

  // Perform batch appends
  if (appends.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, appends.length, appends[0].length).setValues(appends);
    Logger.log(`Appended ${appends.length} new rows to sheet: ${sheetName}`);
  }

  // Sort the sheet by the date column, if date field is present
  if (dateColIndex !== -1 && sheet.getLastRow() > 1) {
    const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
    dataRange.sort({column: dateColIndex + 1, ascending: true}); // +1 because sheet column is 1-indexed
    Logger.log(`Sorted sheet '${sheetName}' by '${dateField}' column.`);
  }

  sheet.autoResizeColumns(1, headers.length);
  Logger.log(`Finished processing and writing data to sheet: ${sheetName}`);
}

/**
 * Deletes rows from a specified sheet that are older than a given number of months.
 * @param {string} sheetName - The name of the sheet.
 * @param {string} dateColumnField - The field name in the headers that contains the date.
 * @param {number} monthsToKeep - Number of months of data to keep.
 */
function deleteOldRows(sheetName, dateColumnField, monthsToKeep) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    Logger.log(`Sheet '${sheetName}' not found. Skipping old row deletion.`);
    return;
  }

  if (sheet.getLastRow() < 2) { // Only headers or empty
    Logger.log(`Sheet '${sheetName}' is empty or only has headers. Skipping old row deletion.`);
    return;
  }

  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const dateColIndex = headers.indexOf(dateColumnField);

  if (dateColIndex === -1) {
    Logger.log(`Date column '${dateColumnField}' not found in sheet '${sheetName}'. Cannot delete old rows.`);
    return;
  }

  // Calculate the threshold date (e.g., 3 months ago from current date)
  // New Date() operates in the script's default timezone (set in Project Settings)
  const thresholdDate = new Date(); 
  thresholdDate.setMonth(thresholdDate.getMonth() - monthsToKeep);
  thresholdDate.setHours(0, 0, 0, 0); // Normalize to start of day for comparison

  Logger.log(`Deleting rows in '${sheetName}' older than ${monthsToKeep} months (threshold: ${thresholdDate.toLocaleDateString(undefined, {timeZone: CONFIG.SCRIPT_TIME_ZONE})}).`);

  // Get all data values (excluding header row)
  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();

  let rowsDeletedCount = 0;
  // Iterate from the last row backwards to safely delete rows without affecting indices of subsequent iterations
  for (let i = values.length - 1; i >= 0; i--) {
    const row = values[i];
    const rowDateValue = row[dateColIndex];

    // Attempt to parse date from various formats
    let rowDate;
    if (rowDateValue instanceof Date) {
      rowDate = rowDateValue;
    } else if (typeof rowDateValue === 'string') {
      try {
        rowDate = new Date(rowDateValue); // CallRail ISO strings parse well
      } catch (e) {
        Logger.log(`Could not parse date from row ${i+2} in '${sheetName}': ${rowDateValue}. Error: ${e.message}. Skipping.`);
        continue; 
      }
    } else {
        Logger.log(`Unexpected date format in row ${i+2} in '${sheetName}': ${typeof rowDateValue}. Skipping.`);
        continue;
    }
    
    // Compare dates. If rowDate is valid and older than thresholdDate, delete.
    if (rowDate && !isNaN(rowDate.getTime()) && rowDate < thresholdDate) {
      // Sheet row index is (i + 2) because data starts from row 2 (header is row 1) and 'i' is 0-indexed for 'values' array.
      sheet.deleteRow(i + 2); 
      rowsDeletedCount++;
    }
  }

  if (rowsDeletedCount > 0) {
    Logger.log(`Successfully deleted ${rowsDeletedCount} old rows from sheet '${sheetName}'.`);
  } else {
    Logger.log(`No rows older than ${monthsToKeep} months found for deletion in sheet '${sheetName}'.`);
  }
}


// --- SPECIFIC DATA FETCHERS ---

function getCalls() {
  const ui = SpreadsheetApp.getUi();
  // We still use getLastMonthDateRange for logging to show WHICH month 'last_month' refers to
  const { startDate, endDate } = getLastMonthDateRange(); 
  ui.alert('Fetching Calls', `Starting to fetch CallRail call data for last month (${startDate} to ${endDate}). This may take a moment...`, ui.ButtonSet.OK);
  Logger.log(`Starting getCalls() for period ${startDate} to ${endDate}.`);

  // Corrected API call: no longer passing explicit dates to fetchCallRailData
  const callData = fetchCallRailData('calls.json', CONFIG.CALL_FIELDS);
  
  processAndWriteData(CONFIG.SHEET_NAME_CALLS, callData, CONFIG.UNIQUE_ID_FIELD, CONFIG.CALL_DATE_FIELD);
  
  ui.alert('Calls Fetched', `Finished fetching CallRail call data. ${callData.length} records processed.`, SpreadsheetApp.getUi().ButtonSet.OK);
  Logger.log('Finished getCalls()');
}

function getForms() {
  const ui = SpreadsheetApp.getUi();
  // We still use getLastMonthDateRange for logging to show WHICH month 'last_month' refers to
  const { startDate, endDate } = getLastMonthDateRange();
  ui.alert('Fetching Forms', `Starting to fetch CallRail form data for last month (${startDate} to ${endDate}). This may take a moment...`, ui.ButtonSet.OK);
  Logger.log(`Starting getForms() for period ${startDate} to ${endDate}.`);

  // Corrected API call: no longer passing explicit dates to fetchCallRailData
  const formData = fetchCallRailData('form_submissions.json', CONFIG.FORM_FIELDS);
  
  processAndWriteData(CONFIG.SHEET_NAME_FORMS, formData, CONFIG.UNIQUE_ID_FIELD, CONFIG.FORM_DATE_FIELD);
  
  ui.alert('Forms Fetched', `Finished fetching CallRail form data. ${formData.length} records processed.`, SpreadsheetApp.getUi().ButtonSet.OK);
  Logger.log('Finished getForms()');
}

/**
 * Main function to run both call and form data fetching for the last month.
 * This is the function to be linked to time-driven triggers.
 */
function runAllMonthly() {
  const ui = SpreadsheetApp.getUi();
  ui.alert('Fetching All Monthly Data', 'Starting to fetch CallRail data for the last month and clean old data. This may take a moment...', ui.ButtonSet.OK);
  Logger.log('Starting runAllMonthly()');

  // --- Step 1: Delete old data ---
  Logger.log('Initiating deletion of old rows...');
  deleteOldRows(CONFIG.SHEET_NAME_CALLS, CONFIG.CALL_DATE_FIELD, CONFIG.MONTHS_TO_KEEP_DATA);
  deleteOldRows(CONFIG.SHEET_NAME_FORMS, CONFIG.FORM_DATE_FIELD, CONFIG.MONTHS_TO_KEEP_DATA);
  Logger.log('Finished deletion of old rows.');

  // --- Step 2: Fetch and process last month's data ---
  getCalls(); 
  getForms(); 

  ui.alert('All Monthly Data Fetched & Cleaned', 'Finished fetching all CallRail data for the last month and cleaned old data.', SpreadsheetApp.getUi().ButtonSet.OK);
  Logger.log('Finished runAllMonthly()');
}

// --- CUSTOM MENU ---

/**
 * Adds a custom menu to the Google Sheet when it's opened.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('CallRail Data (Monthly)')
      .addItem('Fetch Last Month Calls (Manual)', 'getCalls')
      .addItem('Fetch Last Month Forms (Manual)', 'getForms')
      .addSeparator()
      .addItem('Fetch All Last Month Data (Manual)', 'runAllMonthly')
      .addToUi();
}

Comprehensive Step-by-Step Setup Guide for Pull Data

Follow these instructions carefully to set up your CallRail data synchronization and automation in Google Sheets.

Step 1: Create Your Google Sheet

1. Open your web browser and go to Google Sheets: sheets.google.com
2. Click on the + Blank spreadsheet tile to create a new, empty sheet.
3. Rename your spreadsheet to something descriptive, like “CallRail Monthly Data – [Your Company Name]”. (e.g., by clicking “Untitled spreadsheet” at the top-left).
   • The script will automatically create “Calls” and “Forms” tabs within this spreadsheet.

Step 2: Open the Apps Script Editor

1. In your new Google Sheet, go to the top menu: Extensions > Apps Script.
2. A new browser tab will open, showing the Apps Script editor. You’ll likely see an empty Code.gs file.

Step 3: Copy and Paste the Script

1. In the Apps Script editor, delete any existing code in the Code.gs file (e.g., function myFunction() {}).
2. Copy the entire script provided in Section 3 above (from // --- CONFIGURATION --- to addToUi(); }) and paste it into the Code.gs file.

Step 4: Save the Script

1. In the Apps Script editor, click the Save project icon (looks like a floppy disk) in the toolbar, or go to File > Save project.

Step 5: IMPORTANT! Set the Script’s Time Zone

This is crucial for ensuring the automatic deletion of old data and the monthly triggers run accurately based on US Buffalo, NY time.

1. In the Apps Script editor, look at the left-hand sidebar and click on Project settings (the gear icon).
2. Under the General settings section, find “Project time zone”.
3. Click the Change time zone button.
4. From the dropdown menu, select (GMT-05:00) America/New_York.
5. Click “Save project settings” at the top right (looks like a floppy disk again).

Step 6: Authorize the Script (One-Time Process)

You need to grant the script permission to access your Google Sheet and connect to external services (CallRail).

1. Go back to the Editor (the code icon on the left sidebar).
2. In the toolbar, find the dropdown menu that might say runAllMonthly or onOpen. Select onOpen from this dropdown.
3. Click the Run button (the play icon ▶️) next to the dropdown.
4. A dialog will pop up saying “Authorization required.” Click “Review permissions.”
5. Select your Google Account.
6. You might see a warning that “Google hasn’t verified this app.” This is normal for a script you created. Click “Advanced” then “Go to [Project Name] (unsafe).”
7. Review the permissions requested and click “Allow” to grant them.
   • Note: After authorization, the onOpen function will run and create the custom “CallRail Data (Monthly)” menu in your Google Sheet.

Step 7: Run the Script Manually (Initial Data Population & Cleanup Test)

Now, let’s get the initial data into your sheet and test the cleanup.

1. Go back to your Google Sheet tab.
2. You should now see a new custom menu item in the top bar called CallRail Data (Monthly).
3. Click on CallRail Data (Monthly) > Fetch All Last Month Data (Manual).
4. You’ll see pop-up alerts from the script as it starts the process, cleans up old data, and then fetches and writes new data.
5. Wait for the process to complete. You should see “Calls” and “Forms” sheets created (if they didn’t exist) and populated with data from the previous month. Verify that rows older than 3 months are deleted if you had any prior data.

Step 8: Set Up the Automatic Monthly Triggers

You need to create two time-driven triggers to ensure the script runs reliably on the 1st day of each month.

1. Go back to the Apps Script editor tab.
2. On the left sidebar, click the Triggers icon (looks like an alarm clock ⏰).
3. Click the + Add Trigger button in the bottom right corner.Configure Trigger 1 (6 AM on the 1st):
   • Choose which function to run: Select runAllMonthly.
   • Select event source: Choose Time-driven.
   • Select type of time-based trigger: Choose Month timer.
   • Select day of the month: Choose 1st day.
   • Select time of day: Choose 6 AM to 7 AM.
   • Click Save.
   Configure Trigger 2 (Second run within the 6 AM hour, for redundancy/request for “twice”):
   • Click the + Add Trigger button again.
   • Choose which function to run: Select runAllMonthly.
   • Select event source: Choose Time-driven.
   • Select type of time-based trigger: Choose Month timer.
   • Select day of the month: Choose 1st day.
   • Select time of day: Choose 6 AM to 7 AM. (Apps Script will typically space these out within the hour).
   • Click Save.

---

2nd Step: Get the Refined Data from Dump to ROI Sheet: Some Data via API Request

// --- CONFIGURATION FOR ROI REPORT ---
// IMPORTANT: These IDs and API keys are critical.
// Ensure these match your actual spreadsheet IDs, sheet names, and CallRail credentials.

// Your CallRail API Credentials (these are required for direct API calls for historical data)
const CALLRAIL_ACCOUNT_ID = '628189884';          // Your CallRail Account ID
const CALLRAIL_API_KEY = '8e82a8a77a8ffee2612f21008c921b7f'; // Your CallRail API Key
const CALLRAIL_COMPANY_ID = '665808319';          // Your specific Company ID

// The spreadsheet where Calls and Forms data is pulled by your *other* script
const CALLS_FORMS_SPREADSHEET_ID = '1haBOaAJJja4E8za-I6CUbVPsXWzsqkZ9BSFiErZThe8'; 
const CALLS_SHEET_NAME = 'Calls'; // The name of the sheet for calls data in the above spreadsheet
const FORMS_SHEET_NAME = 'Forms'; // The name of the sheet for forms data in the above spreadsheet

// This script will update the sheet 'Guide Call Tracking and ROI' in the spreadsheet it's bound to.
const ROI_REPORT_SHEET_NAME = 'ROI'; 

// Define source categories for aggregation. These must exactly match values in your 'source' column.
const DIRECT_SOURCES = ['Direct'];
const ORGANIC_SOURCES = [
    'Bing Organic', 'Bing Organic & Local', 'DuckDuckGo Organic', 'duckduckgo_organic',
    'Facebook Organic', 'Google Organic', 'Google Organic & Local', 'google_organic',
    'Instagram Organic', 'LinkedIn Organic', 'Nextdoor Organic', 'Organic & Local Search',
    'Organic Search', 'Pinterest Organic', 'Spotify Organic', 'TikTok Organic',
    'X (Twitter) Organic', 'Yahoo Organic', 'Yahoo Organic & Local', 'Yellow Pages Organic',
    'Yelp Organic', 'Youtube Organic', 'chatgpt.com', 'SearchGPT', 'Perplexity',
    'Gemini Organic', 'Google', 'google_local'
];
const GMB_SOURCES = ['Google My Business', 'gmb'];
const PREVIOUS_WEBSITE_NUMBER_SOURCES = ['Previous Website Number'];
const FBCLID_SOURCES = ['Landing: ?fbclid'];
const GOOGLE_ADS_SOURCES = ['Google Ads'];


/**
 * Calculates the start and end dates for a month relative to the current date.
 * E.g., for previous month, use monthsOffset = 1. For month before previous, use 2.
 * @param {number} monthsOffset - How many months back from the current month to calculate. 0 for current month, 1 for last month, 2 for month before last.
 * @param {number} yearsOffset - How many years back.
 * @returns {{startDate: Date, endDate: Date, startString: string, endString: string}} An object with Date objects and formatted strings.
 */
function getRelativeMonthDateRange(monthsOffset, yearsOffset = 0) {
  const now = new Date();
  let year = now.getFullYear();
  let month = now.getMonth(); // 0-indexed

  // Adjust for years offset first
  year -= yearsOffset;

  // Adjust for months offset
  month -= monthsOffset;
  
  // Handle month wrap-around (e.g., month = -1 becomes Dec of prev year)
  if (month < 0) {
    year += Math.floor(month / 12); // Add negative years
    month = month % 12;
    if (month < 0) month += 12; // Ensure month is positive 0-11
  }

  const firstDay = new Date(year, month, 1);
  firstDay.setHours(0, 0, 0, 0); // Set to start of day for precise range

  const lastDay = new Date(year, month + 1, 0); // Day 0 of next month is last day of current month
  lastDay.setHours(23, 59, 59, 999); // Set to end of day for precise range

  const formatDateString = (date) => Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  return {
    startDate: firstDay,
    endDate: lastDay,
    startString: formatDateString(firstDay),
    endString: formatDateString(lastDay)
  };
}


/**
 * Fetches total count of calls or forms directly from CallRail API for a specific date range.
 * This uses start_date and end_date parameters in the API call.
 * @param {string} endpoint - The API endpoint (e.g., 'calls.json', 'form_submissions.json').
 * @param {string} startDateString - Start date in YYYY-MM-DD format (used in API call).
 * @param {string} endDateString - End date in YYYY-MM-DD format (used in API call).
 * @returns {number} The total number of records for the given range, or 0 on error.
 */
function fetchCallRailTotalCount(endpoint, startDateString, endDateString) {
  const accountId = CALLRAIL_ACCOUNT_ID;
  const apiKey = CALLRAIL_API_KEY;
  const companyId = CALLRAIL_COMPANY_ID;

  // We add 'fields=id&limit=1' because we only need the total_records count, not the actual data.
  let url = `https://api.callrail.com/v3/a/${accountId}/${endpoint}?start_date=${startDateString}&end_date=${endDateString}&fields=id&limit=1`; 
  
  if (companyId) {
    url += `&company_id=${companyId}`;
  }

  Logger.log(`Fetching total count from CallRail: ${url}`);

  try {
    const response = UrlFetchApp.fetch(url, {
      method: 'get',
      headers: {
        'Authorization': `Token token="${apiKey}"`
      },
      muteHttpExceptions: true // To get error details in response.getContentText()
    });

    const responseCode = response.getResponseCode();
    const responseText = response.getContentText();

    if (responseCode === 200) {
      const jsonData = JSON.parse(responseText);
      return jsonData.total_records || 0; // CallRail API returns total_records in metadata
    } else {
      Logger.log(`Error fetching CallRail total count from ${url}: HTTP ${responseCode} - ${responseText}`);
      return 0; 
    }
  } catch (e) {
    Logger.log(`Exception when fetching CallRail total count: ${e.message}`);
    return 0;
  }
}


/**
 * Helper function to fetch all data from a specified sheet in a given spreadsheet.
 * @param {string} spreadsheet_id - The ID of the Google Spreadsheet.
 * @param {string} sheet_name - The name of the sheet within the spreadsheet.
 * @returns {{headers: string[], data: any[][]}} An object containing headers and data rows, or null if an error occurs.
 */
function get_data_from_sheet(spreadsheet_id, sheet_name) {
    try {
        const spreadsheet = SpreadsheetApp.openById(spreadsheet_id);
        const sheet = spreadsheet.getSheetByName(sheet_name);
        if (!sheet) {
            Logger.log(`Sheet '${sheet_name}' not found in spreadsheet ID: ${spreadsheet_id}`);
            SpreadsheetApp.getUi().alert('Sheet Not Found', `The sheet '${sheet_name}' was not found in the spreadsheet ID: ${spreadsheet_id}. Please ensure the sheet name and ID are correct in the script.`, SpreadsheetApp.getUi().ButtonSet.OK);
            return {headers: null, data: null};
        }
        
        const data_range = sheet.getDataRange();
        const values = data_range.getValues();
        
        if (!values || values.length < 1) { // Check if there are any values (even just headers)
            Logger.log(`Sheet '${sheet_name}' is empty or only has headers.`);
            return {headers: [], data: []}; // Return empty arrays if sheet is completely empty or only headers
        }
        
        const headers = values[0];
        const data = values.slice(1); // Data starts from the second row
        return {headers: headers, data: data};
    } catch (e) {
        Logger.log(`Error accessing sheet '${sheet_name}' in spreadsheet ID ${spreadsheet_id}: ${e.message}`);
        SpreadsheetApp.getUi().alert('Script Error', `Could not access data from the CallRail '${sheet_name}' sheet. Please check the spreadsheet ID and permissions. Error: ${e.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
        return {headers: null, data: null};
    }
}


/**
 * Helper function to count records based on the source column value.
 * @param {any[][]} data - The data rows to count.
 * @param {string[]} headers - The headers of the sheet.
 * @param {string} source_column_name - The name of the column containing source values (e.g., 'source').
 * @param {string[]} target_sources - An array of source values to count.
 * @returns {number} The count of matching records.
 */
function count_by_source(data, headers, source_column_name, target_sources) {
    if (!data || !headers) {
        return 0;
    }
    
    const source_col_index = headers.indexOf(source_column_name);
    if (source_col_index === -1) {
        Logger.log(`Source column '${source_column_name}' not found in headers for counting. Returning 0.`);
        return 0;
    }

    let count = 0;
    for (let i = 0; i < data.length; i++) {
        const row = data[i];
        if (source_col_index < row.length) {
            // Convert to string and trim to ensure accurate comparison
            const source_value = String(row[source_col_index]).trim(); 
            if (target_sources.includes(source_value)) {
                count++;
            }
        }
    }
    return count;
}

/**
 * Main function to update the ROI report sheet with aggregated call and form data.
 * This function reads from the CallRail data sheets for current month aggregates
 * and makes direct CallRail API calls for specific historical data points.
 */
function update_roi_report() {
    const ui = SpreadsheetApp.getUi();
    ui.alert('Updating ROI Report', 'Starting to update the ROI report. This may take a moment as it fetches current and historical data...', ui.ButtonSet.OK);
    Logger.log('Starting update_roi_report()');

    // --- Data Fetching from Sheets (for A2, B2, Rows 5 & 6 - no internal date filtering) ---
    // 1. Get ALL data from Calls sheet (from the external CallRail spreadsheet)
    const { headers: calls_headers, data: calls_data_from_sheet } = get_data_from_sheet(CALLS_FORMS_SPREADSHEET_ID, CALLS_SHEET_NAME);
    if (calls_headers === null) { 
        Logger.log("Failed to retrieve calls data from sheet. Aborting ROI report update.");
        return;
    }
    
    // 2. Get ALL data from Forms sheet (from the external CallRail spreadsheet)
    const { headers: forms_headers, data: forms_data_from_sheet } = get_data_from_sheet(CALLS_FORMS_SPREADSHEET_ID, FORMS_SHEET_NAME);
    if (forms_headers === null) { 
        Logger.log("Failed to retrieve forms data from sheet. Aborting ROI report update.");
        return;
    }

    // --- Date Ranges for historical API pulls ---
    // Month before last (for C9, C10)
    const monthBeforeLastRange = getRelativeMonthDateRange(2);
    Logger.log(`Month Before Last Range (for API pulls): ${monthBeforeLastRange.startString} to ${monthBeforeLastRange.endString}`);

    // Same month last year (for D9, D10)
    const sameMonthLastYearRange = getRelativeMonthDateRange(1, 1);
    Logger.log(`Same Month Last Year Range (for API pulls): ${sameMonthLastYearRange.startString} to ${sameMonthLastYearRange.endString}`);


    // --- Direct CallRail API pulls for historical totals (C9, D9, C10, D10) ---

    // Total calls for month before last (C9)
    const calls_month_before_last = fetchCallRailTotalCount(
        'calls.json', 
        monthBeforeLastRange.startString, 
        monthBeforeLastRange.endString
    );
    Logger.log(`Calls (Month Before Last - API): ${calls_month_before_last}`);

    // Total forms for month before last
    const forms_month_before_last = fetchCallRailTotalCount(
        'form_submissions.json', 
        monthBeforeLastRange.startString, 
        monthBeforeLastRange.endString
    );
    Logger.log(`Forms (Month Before Last - API): ${forms_month_before_last}`);
    const total_leads_month_before_last = calls_month_before_last + forms_month_before_last;


    // Total calls for same month last year (D9)
    const calls_same_month_last_year = fetchCallRailTotalCount(
        'calls.json', 
        sameMonthLastYearRange.startString, 
        sameMonthLastYearRange.endString
    );
    Logger.log(`Calls (Same Month Last Year - API): ${calls_same_month_last_year}`);

    // Total forms for same month last year
    const forms_same_month_last_year = fetchCallRailTotalCount(
        'form_submissions.json', 
        sameMonthLastYearRange.startString, 
        sameMonthLastYearRange.endString
    );
    Logger.log(`Forms (Same Month Last Year - API): ${forms_same_month_last_year}`);
    const total_leads_same_month_last_year = calls_same_month_last_year + forms_same_month_last_year;


    // --- Get the target ROI Report sheet ---
    const roi_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const roi_sheet = roi_spreadsheet.getSheetByName(ROI_REPORT_SHEET_NAME);
    if (!roi_sheet) {
        ui.alert('Sheet Not Found', `The ROI Report sheet '${ROI_REPORT_SHEET_NAME}' was not found in *this* spreadsheet. Please ensure the sheet name is correct.`, ui.ButtonSet.OK);
        Logger.log(`ROI Report sheet '${ROI_REPORT_SHEET_NAME}' not found in the bound spreadsheet.`);
        return;
    }

    // --- Calculations for Cells (using ALL data from sheets for A2, B2, Rows 5 & 6) ---
    const total_calls_from_sheet = calls_data_from_sheet ? calls_data_from_sheet.length : 0;
    const total_forms_from_sheet = forms_data_from_sheet ? forms_data_from_sheet.length : 0;
    const total_leads_from_sheet = total_calls_from_sheet + total_forms_from_sheet;


    // --- Write to ROI Report Sheet ---
    try {
        // Row 2: Total Calls and Total Leads (from ALL data in source sheets)
        roi_sheet.getRange('A2').setValue(total_calls_from_sheet);
        roi_sheet.getRange('B2').setValue(total_leads_from_sheet);

        // Row 5: Calls by Source (from ALL data in source sheets)
        roi_sheet.getRange('B5').setValue(count_by_source(calls_data_from_sheet, calls_headers, 'source', DIRECT_SOURCES));
        roi_sheet.getRange('C5').setValue(count_by_source(calls_data_from_sheet, calls_headers, 'source', ORGANIC_SOURCES));
        roi_sheet.getRange('D5').setValue(count_by_source(calls_data_from_sheet, calls_headers, 'source', GMB_SOURCES));
        roi_sheet.getRange('E5').setValue(count_by_source(calls_data_from_sheet, calls_headers, 'source', PREVIOUS_WEBSITE_NUMBER_SOURCES));
        roi_sheet.getRange('F5').setValue(count_by_source(calls_data_from_sheet, calls_headers, 'source', FBCLID_SOURCES));
        roi_sheet.getRange('G5').setValue(count_by_source(calls_data_from_sheet, calls_headers, 'source', GOOGLE_ADS_SOURCES));

        // Row 6: Forms by Source (from ALL data in source sheets)
        roi_sheet.getRange('B6').setValue(count_by_source(forms_data_from_sheet, forms_headers, 'source', DIRECT_SOURCES));
        roi_sheet.getRange('C6').setValue(count_by_source(forms_data_from_sheet, forms_headers, 'source', ORGANIC_SOURCES));
        roi_sheet.getRange('D6').setValue(count_by_source(forms_data_from_sheet, forms_headers, 'source', GMB_SOURCES));
        roi_sheet.getRange('E6').setValue(count_by_source(forms_data_from_sheet, forms_headers, 'source', PREVIOUS_WEBSITE_NUMBER_SOURCES));
        roi_sheet.getRange('F6').setValue(count_by_source(forms_data_from_sheet, forms_headers, 'source', FBCLID_SOURCES));
        roi_sheet.getRange('G6').setValue(count_by_source(forms_data_from_sheet, forms_headers, 'source', GOOGLE_ADS_SOURCES));

        // Row 9: Historical Call Totals (DIRECT API PULLS)
        roi_sheet.getRange('C9').setValue(calls_month_before_last);
        roi_sheet.getRange('D9').setValue(calls_same_month_last_year);

        // Row 10: Historical Lead Totals (DIRECT API PULLS)
        roi_sheet.getRange('C10').setValue(total_leads_month_before_last);
        roi_sheet.getRange('D10').setValue(total_leads_same_month_last_year);

        ui.alert('ROI Report Updated', 'Successfully updated the ROI report with current month data from sheets and historical data from CallRail API.', SpreadsheetApp.getUi().ButtonSet.OK);
        Logger.log('Finished update_roi_report() successfully.');

    } catch (e) {
        ui.alert('Write Error', `An error occurred while writing data to the ROI Report sheet: ${e.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
        Logger.log(`Error writing to ROI report sheet: ${e.message}`);
    }
}

/**
 * Adds a custom menu to the Google Sheet when it's opened.
 * This 'onOpen' function will only be present in the ROI Report spreadsheet's script.
 */
function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('ROI Report')
        .addItem('Update ROI Report (Manual)', 'update_roi_report')
        .addToUi();
}

Step-by-Step Setup Guide for the REFINED ROI Report Script:

This process involves updating the existing Apps Script project that is already associated with your ROI Report Spreadsheet.

1. Open the Apps Script Editor for your ROI Report:
   • Go to your ROI Report Google Sheet (https://docs.google.com/spreadsheets/d/1xCHRSbbbESAfUz2qCtcjqcCvgbCKPckQiLN3GSMy2wM/).
   • Click Extensions > Apps Script. This will open the Apps Script editor for this specific spreadsheet.
2. Replace ALL Existing Code:
   • In the Apps Script editor, delete all the code in your Code.gs file.
   • Copy the ENTIRE refined code block provided above (from // --- CONFIGURATION FOR ROI REPORT --- down to the end of function onOpen()) and paste it into the empty Code.gs file.
3. Verify Configuration:
   • Crucially, double-check your CallRail API credentials (CALLRAIL_ACCOUNT_ID, CALLRAIL_API_KEY, CALLRAIL_COMPANY_ID) as well as CALLS_FORMS_SPREADSHEET_ID, CALLS_SHEET_NAME, FORMS_SHEET_NAME, and ROI_REPORT_SHEET_NAME constants at the top of the script. Ensure they are all accurate for your setup.
4. Save the Script:
   • Click the Save project icon (floppy disk).
5. Re-Authorize the Script (CRITICAL!): This script makes direct API calls to CallRail, which requires additional permissions. You MUST re-authorize it every time you change the scope (e.g., adding API calls).
   • In the Apps Script editor, select onOpen from the function dropdown.
   • Click the Run button (play icon).
   • When prompted “Authorization required,” click “Review permissions.”
   • Select your Google Account.
   • If you see a warning that “Google hasn’t verified this app,” click “Advanced” then “Go to [Project Name] (unsafe).”
   • Review the permissions requested (it will ask for permission to connect to external services and to view/manage your spreadsheets) and click “Allow” to grant them.
6. Confirm Project Time Zone (if you haven’t already):
   • In the Apps Script editor, go to Project settings (gear icon).
   • Under “General settings,” ensure “Project time zone” is set to (GMT-05:00) America/Chicago. Save settings if you change it.
7. Verify/Set Up the Automatic Trigger:
   • In the Apps Script editor, go to the Triggers icon (alarm clock ⏰).
   • Make sure you have a trigger set up for:
     • Function: update_roi_report
     • Event source: Time-driven
     • Type: Month timer
     • Day of month: 1st day
     • Time of day: 6 AM to 7 AM
   • If it’s not there, add it. If it’s incorrect, delete and re-add it.
8. Run Manually to Test:
   • Go back to your ROI Report Google Sheet.
   • Click on ROI Report > Update ROI Report (Manual).
   • Verify that cells C9, D9, C10, and D10 are now populated with the correct historical data from CallRail API.
   • Also, verify that cells A2, B2, B5-G5, and B6-G6 are populated based on all the data currently present in your CallRail source sheets (1haBOaAJJja4E8za-I6CUbVPsXWzsqkZ9BSFiErZThe8). You can check the Logger output in Apps Script (under Executions) for details on the API calls made.

• Published at May 3, 2023
• Category: Uncategorized