Convert onEdit script to manually running script

Question

The script has the purpose of writing the datetimestamp to a specific cell the moment when a cell in column 4 has the text Assign.

I have a script that theoretical works but I'm getting my data from App Sheet. The problem with this is that App Sheet writes data into my sheet but the script wont see it as edited cells, so it wont write the time stamp.

But my knowledge about app-scripts is pretty bad. And I am getting errors with the source line and the col, val lines.

function onEdit(e) {

  var sh =  e.source.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var val = sh.getRange(row, 4).getValue();


  //check if sheet is 'Blad1' & value is 'Assign'
  if (sh.getSheetName() === 'Blad1' && val == 'Assign') {
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy hhmmss');

    //set date in column 14 for same row
    sh.getRange(row, 14).setValue(date);

  }
}

I want to convert my script to a manually run script with a time-based trigger of 1 min. That way I hope the script will see the changed cell to Assign.

You mean that you are using a Google Apps Script to write to the sheet? Why don't you simply add the needed changes to your other script, rather than running two? — sinaraheneba
@Altigraph he means he's using AppSheet. User mentioned this on another question earlier today. — ross

Oleg Valter Oleg Valter · Accepted Answer · 2019-06-05T11:56:27

The problem is rooted in apps script triggers restrictions, specifically in their inability to listen to script-based events. I would suggest deploying your script as a WebApp with doGet() / doPost() functions to listen to API requests and, if I am correct in assuming that AppSheet works similarly to Zapier, etc., add a step that calls your WebApp after making changes to your Spreadsheet.

Please, see this guide on WebApps, its pretty straightforward to follow.

P.s. Btw, refrain from creating triggers acting as event listeners, you will easily cap your quotas!

Convert onEdit script to manually running script

1 Answers