How to send data to Google Sheets directly

Step 1: Go to https://docs.google.com/spreadsheets

Step 2: Create your Google Sheet by clicking the “Blank Spreadsheet”

Step 3: In this example we’re going to send the first name, last name and email address. So we’re setting up 3 columns and naming the first row.

Step 4: Go to Extensions > App Script

Step 5: Copy and paste this code

// Code.gs file in Google Apps Script

function doPost(e) {
  try {
    // Parse the incoming data
    const data = JSON.parse(e.postData.contents);
    
    // Open your Google Sheet by ID
    // Replace 'YOUR_SPREADSHEET_ID' with your actual spreadsheet ID
    const sheet = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID').getActiveSheet();
    
    // Append the data as a new row
    sheet.appendRow([
      data.firstName,
      data.lastName,
      data.email
    ]);
    
    // Return success response
    return ContentService
      .createTextOutput(JSON.stringify({
        'status': 'success',
        'message': 'Data added successfully'
      }))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    // Return error response
    return ContentService
      .createTextOutput(JSON.stringify({
        'status': 'error',
        'message': error.toString()
      }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

function doGet(e) {
  return ContentService
    .createTextOutput(JSON.stringify({
      'status': 'success',
      'message': 'GET request received'
    }))
    .setMimeType(ContentService.MimeType.JSON);
}

Step 6: In your code (Line 10) Replace YOUR_SPREADSHEET_ID with your google sheet spreadsheet ID. The spreadsheet ID is after the d/ and before the /edit. Here’s an example:

Step 7: Click Deploy > New Deployment

Step 8: Select Type > Web App

Step 9: Set the description and Select “Anyone” from the “Who has access” dropdown

Step 10: Click “Authorize Access”

Step 11: If you see the Google hasnt verified this app message, just Proceed and Allow

Step 12: Copy the Web App URL

Step 13: In your Leadshook decision tree, create a webhook node, paste the copied Web App URL in your Request URL. It should be configured like this:

Step 13: Save and Publish your decision tree and run test. When you test and submit lead with first_name, last_name, and email, it will be sent to your google sheet.

NOTE: If you need to add more columns in your google sheet to send more data or fields, you can add them in your code in line 17. Here’s an example screenshot

In your Webhook JSON payload should be like this

Tagged: