Google sheet integration for dynamic record update

This guide will walk you through integrating Google sheets with your AI-agent to automatically update records based on user interactions.

Scenario

Consider that you have created a campaign to notify users about current offers, and you want to automatically update a Google sheet with user interaction details when the campaign is sent. The goal is to capture and insert the following information into the Google sheet:

• Template name: Name of the template used in the campaign.
• Quick reply clicked: User's selected quick reply option.
• Campaign ID: Unique identifier for the campaign.
• User phone number: Contact number of the user.
• Timestamp: Date and time of the interaction.

Prerequisites

Consider the following prerequisite to automatically update records in Google sheet:

• Google sheets integration: Configure the integration with Google sheets.
• Create an event: Set up an event in the Engagement section to trigger the data capture flow.

Steps to automatically update a Google sheet

To configure a bot flow that dynamically updates a Google sheet based on user interactions, follow these steps:

1. Go to the flow editor and select the start trigger type as Event and choose the event that you have created.

   

2. Add a Variable node to store to store all template values retrieved from the quick reply event and the values of the Google sheets nodes:

   • spreadSheetID: Unique Google Sheet ID.
   • range: Defines the cell range for data insertion/deletion/updates.
   • majorDimension: Specifies how data is should be arranged (ROWS or COLUMNS).

3. To append new entries in new rows and to avoid overwriting existing records, use the Google sheets node to retrieve existing sheet data and store the result in an object variable.

Sample success response of GetValuesFromSheet:

{
  "spreadsheetId": "1FXBz2k8m9Ys3zkJy6n8G4237q1x43Tq4nu6CdM1A66Y",
  "valueRanges": [
    {
      "range": "Sheet1!A1:A5",
      "majorDimension": "COLUMNS",
      "values": [
        [
          "J",
          "O",
          "H",
          "N"
        ]
      ]
    }
  ]
}

4. Connect Analytics node to the Google Sheets node’s fallback to capture any errors or event data for further analysis.

   

5. After retrieving the sheet records, add a Function node to insert the data. The function node performs two operations:

   • Adjust the range value: The function increments the row index by +1 to ensure new data is inserted into the next available row, avoiding any overwriting of existing records.

   • Output: The function's output should be saved in an object variable that includes the array of values to be inserted into the sheet and the updated range for data placement.

     

A sample code to get the total length of records and to populate an array variable for inserting the records in the sheet:

return new Promise(resolve => {
        // Your logic goes here

    let templateName = data.variables.template_name
    let phone = data.variables.phone
    let campaignid = data.variables.campaignId

    let qrclicked = data.variables.QRclicked

    let currentTime = new Date();
    

    let currentOffset = currentTime.getTimezoneOffset();
    let ISTOffset = 330;   // IST offset UTC +5:30 
    let ISTTime = new Date(currentTime.getTime() + (ISTOffset + currentOffset) * 60000);
    console.log(ISTTime, " istToday ")
    let hoursIST = ISTTime.getHours()
    let minutes = ISTTime.getMinutes()
    // console.log(hoursIST, " hoursIST ")
    // console.log(minutes, " minutes ")

    let date = ISTTime.getDate()
    let month = ISTTime.getMonth() + 1
    let year = ISTTime.getFullYear()
    let day = ISTTime.getDay()
    console.log(month, "month ==")

    console.log(day, "day ==")

    let d = `${date}/${month}/${year}`
   let t

    if (hoursIST >= 0 && hoursIST < 12) {
        t = " AM";
    } else if (hoursIST >= 12 && hoursIST < 24) {
      t = " PM";
    }
    let time = `${d}, ${hoursIST}:${minutes} ${t}`
    console.log(time, " time ")


    let myArray = [
                    [phone], 
                    [qrclicked],
                    [templateName],
                    [campaignid],
                    [time]
                     ]

 console.log(myArray, " ==== my array ====")
 
    let y = data.variables.obj.valueRanges[0].values.length
    // console.log(data.variables.obj, " === data.variables.obj")
    console.log(y, "====== Number of Records =====" )
 //Range = Main!A:Z
    let range = `Main!A${y + 1}:Z`


    let obj = {
        "a": myArray,
        "b": range
    }

     console.log(obj," === obj ===")
        resolve(obj);
    });                                 

6. Add a Variable node to modify the majorDimension variable to "COLUMNS". This ensures each array record is added to a separate column within the sheet.

   

Example: If your data is:

 [
    [1, 2, 3],
    [4, 5, 6]
]

• If major dimension is rows the data in the sheets will be added as vertically

  

• If the major dimension is COLUMNS then the data shall be added as horizontally.

  

7. In Google sheets nodes, define the following:

   1. Select Insert/UpdateValuesInSheet option from Google Sheets node.
   2. Pass the parameters such as majorDimension, Range (updated value from step 5), values (data array to be inserted), spreadSheetID.
   3. Store the node output in an object variable.

   

8. Connect Analytics node to the Google sheet's node fallback. This node will capture the event name and error response for analysis.

   

The screenshot below illustrates how user interaction data from a campaign is automatically inserted into a Google sheet: