Google sheets "watch changes" workaround

Issue Summary:

I’m using a Next.js 15 app that updates a Google Sheet via the Google Sheets API. I have a Make.com webhook set up to detect new data, but it only triggers when I manually edit the sheet. However, when my Next.js app updates the sheet via the API, the webhook does not receive any data.

What I’ve Tried:

  • Confirmed that the API updates the sheet successfully.
  • Checked that the Make.com webhook works for manual edits.
  • Verified that the webhook is set to detect new/updated rows.

So i would like to work on a workaround for this, using a regular webhook trigger (Instead of the “Watch Changes” module) and a Apps Script around the “onChange” Trigger.

This guy seems to have figured the problem out, and has made a workaround for it on the google apps script platform. i would love to copy their workaround script, but i cant communicate with them. ----> nick_cph

Hi @Shay_zinger, I can confirm that watch changes only works on user-entered edits. If you know that onChange will work, it’s fairly simple. You’ll have to create a script like this :

function onChange(e) {
  var url = "https://hook.eu1.make.com/YOUR_WEBHOOK";
  
  // Prepare the payload if needed (you can customize the data you want to send)
  var payload = {
    "key1": "true",
    "key2": "value2"
  };
  
  // Set up the options for the HTTP POST request
  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  // Send the request to the webhook
  var response = UrlFetchApp.fetch(url, options);
  
  // Log the response (optional)
  Logger.log(response.getContentText());
}

Then go in ‘triggers’ on left sidebar and ‘add a trigger’. choose the function onChange() and in the end choose Trigger on change in the menu. Don’t forget to put your webhook link in this script. That should be it.

Mention me if you have more questions.
kud

1 Like

It works! thank you very much
Do you know how to specify a range of cells for it to trigger the script?

@Shay_zinger, Emm you can get range of where the (e) event happened and do if-else condition like this :

var sheetTarget = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MY SHEET NAME");
var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

if (currentSheet != sheetTarget) {return}

const rangeTarget = "MY TARGET RANGE IN A1:B2 FORMAT"
if (e.changeType === "EDIT") {
    const range = e.source.getActiveRange();
    if (!isRangeInsideAnother(range, rangeTarget)){return}
}
else {return}

To be inserted just before “var url = “…”;”


This is what chatGPT offers to check if range is in another range :

function isRangeInsideAnother(range, rangeTarget) {
  // Get start and end row/column for range
  var startRow1 = range.getRow();
  var startCol1 = range.getColumn();
  var endRow1 = startRow1 + range.getNumRows() - 1;
  var endCol1 = startCol1 + range.getNumColumns() - 1;

  // Get start and end row/column for rangeTarget
  var startRow2 = rangeTarget.getRow();
  var startCol2 = rangeTarget.getColumn();
  var endRow2 = startRow2 + rangeTarget.getNumRows() - 1;
  var endCol2 = startCol2 + rangeTarget.getNumColumns() - 1;

  // Check if range is completely within rangeTarget
  return (
    startRow1 >= startRow2 && endRow1 <= endRow2 &&
    startCol1 >= startCol2 && endCol1 <= endCol2
  );
}

P.S. It might not work because onChange(e) usually does not return a range… onEdit(e) does this. Mention me if u have more questions

I added the range detection logic you suggested and now the script doesnt run on change. any idea how to fix it?

function onChange(e) {

var sheetTarget = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

if (currentSheet != sheetTarget) {return}

const rangeTarget = "C2:C380000"
if (e.changeType === "EDIT") {
    const range = e.source.getActiveRange();
    if (!isRangeInsideAnother(range, rangeTarget)){return}
}
else {return}

  var url = "https://hook.eu2.make.com/(mywebhook)";
  
  // Prepare the payload if needed (you can customize the data you want to send)
  var payload = {
    "key1": "true",
    "key2": "value2"
  };
  
  // Set up the options for the HTTP POST request
  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  // Send the request to the webhook
  var response = UrlFetchApp.fetch(url, options);
  
  // Log the response (optional)
  Logger.log(response.getContentText());
}

@Shay_zinger, For this use case we could just look if column is C, instead of using advanced range-matching function. It has to be tested and will take some time.

Try this :

function onChange(e) {

var sheetTarget = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

if (currentSheet != sheetTarget) {Logger.log("not the same sheet");return}

if (e.changeType === "EDIT") {
    var firstColumn = range.getColumn();
    var numColumns = range.getNumColumns(); 
    var columnTarget = 3; // Column C

    // Check if column C is within the edited range
    if (firstColumn > columnTarget || columnTarget-firstColumn >= numColumns) {
        Logger.log("column C is not changed");
        return
    }
}
else {Logger.log("not edit");return}

  var url = "https://hook.eu2.make.com/1z5imh6qd7oo42jg5itmn616ox8qtnbt";
  
  // Prepare the payload if needed (you can customize the data you want to send)
  var payload = {
    "key1": "true",
    "key2": "value2"
  };
  
  // Set up the options for the HTTP POST request
  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  // Send the request to the webhook
  var response = UrlFetchApp.fetch(url, options);
  
  // Log the response (optional)
  Logger.log(response.getContentText());
}

Look in ‘executions’ tab in apps script to look at Logs if there are any.

I’ve encountered the same problem here.

I’m unsure on how to setup next.js 15 and run this script, can you offer any help?

@Shay_zinger

Hi, have you managed to get this working? I could really do with your help.

@kudracha
how do I edit the payload so that it pulls the ‘name’ and ‘phonenumber’ fields from the sheet?