How to Automatically Trigger a Webhook on New Row Addition in Google Sheets Using Google Apps Script?

Question

I’m working on a feature where a client can link their Google Sheet to my system. Whenever a new row is added to the sheet, the system should send the new order data to a webhook. My initial approach was to use Google Apps Script with an onEdit trigger to detect new rows and send a request to the webhook using UrlFetchApp.fetch().

However, I discovered that simple triggers like onEdit don’t have the necessary permissions to send requests to external applications. To work around this, I created a separate function to handle the edit event and manually trigger the webhook request. But this requires setting up the trigger manually, which isn’t ideal for my use case.

Here’s what I’ve tried so far:

1. I set up an onEdit trigger to detect changes in the sheet.
2. I created a separate function to handle the edit event and send data to the webhook using UrlFetchApp.fetch().
3. I used the Google Apps Script API to inject the script into the client’s spreadsheet programmatically.

Here’s an example of the code I used to inject the script:

oauth2Client.generateAuthUrl({
  access_type: 'offline',
  scope: SCOPES,
});
oauth2Client.setCredentials(TOKENS);

// Extract spreadsheet ID from the URL
const spreadsheetId = spreadsheetUrl.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/)[1];
console.log('Extracted Spreadsheet ID:', spreadsheetId);

// Verify the spreadsheet ID is valid
if (!spreadsheetId) {
  throw new Error('Invalid spreadsheet URL. Could not extract spreadsheet ID.');
}

// Apps Script content
const scriptContent = `
  function onEdit(e) {
    const range = e.range;
    const sheet = range.getSheet();

    // Check if the edit is in the first column (column A) and a new row is added
    if (range.getColumn() === 1 && range.getRow() > 1) {
      const newRowData = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];

      // Prepare the payload to send to the webhook
      const payload = JSON.stringify({
        spreadsheetId: e.source.getId(),
        sheetName: sheet.getName(),
        rowData: newRowData,
        rowIndex: range.getRow(),
      });

      // Send the data to the webhook
      const options = {
        method: 'post',
        contentType: 'application/json',
        payload: payload,
        muteHttpExceptions: true, // To avoid throwing errors for non-2xx responses
      };

      try {
        const response = UrlFetchApp.fetch('https://my-webhook-url.com', options);
        console.log('Webhook response:', response.getContentText());
      } catch (error) {
        console.error('Error sending data to webhook:', error);
      }
    }
  }
`;
// Manifest file content
const manifestContent = JSON.stringify({
  timeZone: "America/New_York",
  dependencies: {},
  exceptionLogging: "STACKDRIVER",
  oauthScopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request"
  ],
});

// Inject the script using the Apps Script API
const script = google.script({ version: 'v1', auth: oauth2Client });

console.log('Creating script project...');
const createResponse = await script.projects.create({
  requestBody: {
    title: 'Webhook Script',
    parentId: spreadsheetId,
  },
});

const scriptId = createResponse.data.scriptId;
console.log('Script project created with ID:', scriptId);

console.log('Updating script content...');
await script.projects.updateContent({
  scriptId,
  requestBody: {
    files: [
      {
        name: 'Code',
        type: 'SERVER_JS',
        source: scriptContent,
      },
      {
        name: 'appsscript',
        type: 'JSON',
        source: manifestContent,
      },
    ],
  },
});

console.log('Script injected successfully!');

My Questions:

1. How can I set up a Google Apps Script function that automatically triggers when a new row is added to the sheet without requiring manual trigger setup?
2. How can I ensure that the script has the necessary permissions to send requests to an external webhook?
3. Are there any better alternatives to fetching user data from Google Sheets into my system than using Google Apps Script and webhooks?

Any guidance or suggestions would be greatly appreciated!

Answer 1

You should use the installable trigger On change. The event object includes the property changeType. When a new row is inserted, this property's value is "INSERT_ROW".

Please remember that when writing on an existing blank row, the value of changeType will be "EDIT" instead of "INSERT_ROW".

An installable trigger can be created manually or by using code, for the details see https://developers.google.com/apps-script/guides/triggers/installable and https://developers.google.com/apps-script/guides/triggers/events.

Below is a snapshot of the Add Trigger dialog, showing the dropdown to select the trigger type with the On change value chosen.