put current date to a cell if it is filled with zapier-CodePudding

I found this script:


function onEdit () {
    var s = SpreadsheetApp.getActiveSheet (); 
    if (s.getName () == "sheet_name") { 
    var r = s.getActiveCell (); 
    if (r.getColumn () == 1) { 
    var nextCell = r.offset (0, 1); 
    if (nextCell.getValue () === '')  
    nextCell.setValue (new Date()); 
} 
} 
}

It works if I fill one cell by myself and puts current date to another cell in the right. BUT if I use Zapier to export my data from Todoist to Google Sheets this script doesn't work. It only works if I change something manually.

Is there any way to make a script which will fill a cell I need with a today date when Zapier export data and fills cells automatically?

CodePudding user response：

Suggestion:

As what Tanaike mentioned, you need to rename your function to something else aside from onEdit() since onEdit is a reserved function name for App Script and use onChange trigger.

But based on how Zapier works, the reason why the current code you have provided is not working is because exports from Zapier is not detected as an active cell, so we would need to revamp the entire code.

Try this instead:

function onZapierUpdate() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Sheet2'); //my test sheet is Sheet2
  var range = sheet.getRange(2,1,sheet.getLastRow()-1, 2);
  var data = range.getValues();
  data.forEach(x => x[0] != "" ? x[1] = new Date() : x);
  range.setValues(data);

}

After saving the script, set this on an onChange trigger like so:

Now whenever Zapier exports the data, it changes the content of the spreadsheet which means onChange trigger will take effect.

Reference: https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder#onChange()