Tuesday, July 18, 2017

Quick work around to Google Home - IFTTT bug

If you want to set up a take a memo applet like my Take a memo you have the problem that the created date is not getting added to the sheet. To get around this you need to add a trigger to the sheet. To do this open the script editor

Paste this in the script editor
function onEdit() {
  // Use start & stop to time operations
  var start = new Date().getTime();
  var active = SpreadsheetApp.getActiveSheet().getDataRange();

  // We want the size of the sheet, so will select ranges across and down the
  // whole sheet. Cannot use getDataRange(), as it selects only occupied cells.
  var numCols = active.getLastColumn()
  var numRows = active.getLastRow();

  var stop = new Date().getTime();
  var timeToMeasure = (stop-start);
  //Logger.log("Sheet is "+numCols+" by "+numRows+".");
  start = new Date().getTime();
  var cell = active.getCell(numRows ,3);
  cell.setNumberFormat("MMM/dd/yyyy").setValue(Utilities.formatDate(new Date(), "CST", 'MMM dd yyyy HH:mm:ss'));
  //cell.setNumberFormat('MMM dd yyyy HH:mm:ss');
//  cell.setNumberFormat("MMM/dd/yyyy");

  var stop = new Date().getTime();
  var timeToStore = (stop-start);  

 //   Browser.msgBox("Sheet is "+numCols+" by "+numRows+"."
 //                 +" ("+timeToMeasure+"ms to measure, "+timeToStore+"ms to store.)");

Run the script so it will ask you permission to link by clicking on the run icon.

Now open the triggers editor
Add a trigger like this
Now when IFTTT adds a row to the sheeet (or any edit is made) at time stamp will be placed in column C of the bottom used row.

No comments:

Post a Comment