Google Sheets: Auto update using script - not working

Question

I've created a sheet which IFTTT automatically adds a row to when the air quality in my house changes. That part is working correctly.

I'm trying to implement a script that adds a timestamp to the new row, and then another column calculates the duration that the air quality stayed the same.

The script works if I have the speadsheet open, and edit a cell manually. It does NOT work when IFTTT makes the update. I have setup an trigger than is supposed to run this script anytime an edit is made to the sheet.

Here is the script:

function onEdit() {

var s = SpreadsheetApp.getActiveSheet();

if( s.getName() == "MBAP" ) {

var r = s.getActiveCell();

if( r.getColumn() == 1 ) {

var start = r.offset(0, 3);

start.setValue(new Date()).setNumberFormat("MM/DD/YY HH:mm:ss");
}
}
}

Thank you for your help!

Can I ask you about the method for receiving data from IFTTT? — Tanaike
Thank you for replying. From your replying and your script, I proposed a sample script as an answer. Could you please confirm it? At first, please confirm whether my understanding of your goal is correct. If I misunderstood your question and that was not the direction you want, I apologize. — Tanaike

Tanaike Tanaike · Accepted Answer · 2020-04-10T05:05:16

You have already done the setting for IFTTT side.
- It's "Webhooks" as "THIS".
- It's "Add row to spreadsheet" of "Google Sheets" as "THAT".
When the IFTTT appends a row which have the values of 3 columns (the columns "A", "B" and "C"), you want to put the date to the column "D" of the appended row.
The sheet name is "MBAP".
You want to achieve this using Google Apps Script.

Issue and workaround:

Unfortunately, in this case, the OnEdit event trigger of the simple trigger cannot be used, because the Spreadsheet is updated from outside without the browser. So it is required to use other method.

When I saw "Add row to spreadsheet" of "Google Sheets" as "THAT", I though that the values might be appended by Sheets API. So when I tested the OnChange event trigger for this situation, it was found that when the row is appended, the event trigger was fired. Ref From this result, it was found that "Google Sheets" of IFTTT puts the values to Google Spreadsheet using Sheets API.

In this answer, your goal is achieved by a script executing with the OnChange event trigger.

Usage:

1. Prepare sample script.

The sample script for this situation is as follows. Please copy and paste the following script to the container-bound script of the Spreadsheet which is put from IFTTT.

function onChange(e) {
  const sheet = e.source.getSheetByName("MBAP");
  sheet.getRange(sheet.getLastRow(), 4)
    .setValue(new Date())
    .setNumberFormat("MM/DD/YY HH:mm:ss");
}

2. Install OnChange event trigger.

Please install the OnChange event trigger to the function of onChange. By this, when the values are put to the Spreadsheet, onChange is run by the OnChange event trigger.

3. Test sample script.

In order to test the sample script, please request to the webhook of IFTTT. By this, the timestamp of new Date() is put to the last column of the appended row put by IFTTT.

Note:

In this sample script, it supposes that your applet of IFTTT is set as "Webhooks" to "THIS" and "Add row to spreadsheet" of "Google Sheets" to "THAT".

Reference:

Installable Triggers