How can I trigger the script when the cell value changes by an API call, not a user manually making changes?

0
votes

I am using Temboo to update a Google sheet with sensor data from an Arduino. I am using a script to add time and date stamps to each data entry however since the data is not being entered manually, I am unable to use the onEdit() trigger. I have also tried creating an onChange() trigger as seen below without any luck (trigger parameters were Run: onChange, Events: From spreadsheet on change). Is there a way to do this with or without an installable trigger?

function onChange(e){
  var timezone = "GMT+8";
  var date_format = "MM/dd/yyyy";
  var time_format = "hh:mm:ss";

  var updateColName = "sensor";

  var DateColName = "date";
  var TimeColName = "time";

  // update time and date if the sheet is updated via an api call 
  if (e.changeType == "OTHER") { 

      var sheet = event.source.getSheetByName('Sheet1'); //Name of the sheet where you want to run this script. 
      var actRng = event.source.getActiveRange();
      var editColumn = actRng.getColumn();
      var index = actRng.getRowIndex();
      var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

      var dateCol = headers[0].indexOf(DateColName);
      var timeCol = headers[0].indexOf(TimeColName);

      var updateCol = headers[0].indexOf(updateColName);
      updateCol = updateCol + 1;

      if (dateCol > -1 && timeCol > -1 && index > 1 && editColumn == updateCol) {
        // only timestamp if 'Last Updated' header exists, but not in the header row itself! 

        var cellDate = sheet.getRange(index, dateCol + 1);
        var cellTime = sheet.getRange(index, timeCol + 1);

        var date = Utilities.formatDate(new Date(), timezone, date_format);
        var time = Utilities.formatDate(new Date(), timezone, time_format);

        cellDate.setValue(date);
        cellTime.setValue(time);
    }

  }
};

Thanks in advance!

2
google-apps-scriptgoogle-sheetstriggers
Have you thought of inserting time, date by coding it in audrino itself? – Jack Brown
I tried to use the time library but I was having problems getting the actual time (ie. syncing with my computer's time) to print and not the elapsed time since the sketch started running. If you believe that would be easier, I'd be open to trying it again! – Cassandra

2 Answers

1
votes

Based on this answer, it looks like there is no direct solution for triggering onEdit() if cells are changed but not by the user. I implemented a workaround using a time triggered script mentioned here.

-1
votes

I had a similar problem not to long ago. I tried everything and i ended up with installable trigger onChange

 function onChange(e) {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('name of the function you need to trigger')
.forSpreadsheet(ss)
.onChange()
.create();
}

You need to save the script after you insert the above code and run the onChange function once to install the trigger. You can verify if it's installed by open "current project's trigger" Hope this help's!