Import Leads and Custom Dates with Google Sheets

This guide walks you through integrating your Google Sheets with ABC Sales AI, specifically focusing on how to properly sync custom date fields like birthdays.

Phase 1: Prepare ABC Sales AI

Before touching any code, set up the destination for your data in ABC Sales AI.

1. Log in to your account at app.abcsalesbot.com.
2. Navigate to Custom Fields.
3. Create a new field. Enter birthday as the Name and select Date as the Type.
4. Go to Integrations from the main menu.
5. Select the Google Sheets Integration and click the Copy button to copy the integration code to your clipboard.

Phase 2: Set Up Your Google Sheet

Your Google Sheet needs to be formatted precisely so the script can read your data correctly.

1. Open a new or existing Google Sheet.
2. Rename the specific tab at the bottom of the screen to your preferred name (e.g., birthday).
3. Create the exact column headers in Row 1: name, phone, email, and birthday.
4. Highlight the entire birthday column to format it. The API requires a strict date format (YYYY-MM-DD).
5. In the top menu, click Format > Number > Custom date and time.
6. Select or type yyyy-mm-dd (so it looks like 2003-12-01) and click Apply.

Phase 3: Configure Google Apps Script

Now, paste and modify the code to connect the Sheet to ABC Sales AI.

1. In your Google Sheet, click Extensions > Apps Script.
2. Delete any existing code in the editor, and paste the code you copied from ABC Sales AI.
3. Update the SHEET_NAME variable at the top of the script to match your bottom tab exactly (e.g., const SHEET_NAME = "birthday";).
4. Ensure your REQUIRED_FIELDS match your column headers exactly.
5. Under CUSTOM_FIELDS, map the birthday column by replacing <EDIT_THIS_VALUE> with "birthday".

Phase 4: Important Code Modifications

You need to make four critical tweaks to ensure the dates and automations are read properly without throwing an "Invalid payload" error. Press Ctrl + F (or Cmd + F on Mac) to find and replace the following code blocks.

Tweak 1: Fix the lastRow Variable

• Find: var lastRow = e.range ? e.range.getRow() : sheet.getLastRow();
• Replace with: var lastRow = (e && e.range) ? e.range.getRow() : sheet.getLastRow();

Tweak 2: Fix the Date Formatting (Part 1)

• Find: var rowData = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
• Replace with: var rowData = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getDisplayValues()[0];

Tweak 3: Fix the Date Formatting (Part 2)

• Find: var rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
• Replace with: var rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getDisplayValues()[0];

Tweak 4: Fix the Automation "Null" Bug

Only do this tweak when your AUTOMATION_SUBSCRIBE is null. If automations are left as null, the system will reject the sync. You must replace the function that prepares this data.

1. Find the line that starts with function prepareDataForSalesbot(rowData, indices) {.
2. Highlight from that line all the way down to the return data; } right before the very last sendDataToSalesbot function.
3. Delete that highlighted section and replace it with the exact block of code below.

Phase 5: Authorize and Add Triggers

Finally, grant the script permission to run and set up triggers so it runs automatically when you add new leads.

1. Click the Save icon (floppy disk) at the top of the editor, then click Run.
2. An "Authorization Required" prompt will appear. Click Review permissions and choose your Google account.
3. Google will show a warning screen. Click the grey Advanced text at the bottom, then click Go to Untitled project (unsafe), and click Allow.
4. Go to the left-hand menu and click the Triggers icon (it looks like a clock).
5. Click + Add Trigger in the bottom right corner. Set the function to handleNewData and the event type to On edit. Click Save.
6. Click + Add Trigger again. Set the function to handleNewData and the event type to On change. Click Save.

Setup complete! Moving forward, whenever you add a new lead and their birthday into this Google Sheet, it will automatically sync to ABC Sales AI.

Important Usage Remarks

Pay close attention to how you enter phone numbers in the phone column:

• Malaysia: Must start with 60 (e.g., 60123456789).
• Singapore: Just input the standard 8-digit number (e.g., 81234567). The script will handle it automatically.
• All other countries: Must include the full + country code at the beginning (e.g., +44... for the UK).