Custom Google Sheet Frozen Timestamp Formula

Question

Prefacing this with the fact that I'm not especially versed in Google Sheet scripting, just fairly decent at Googling and making stuff work for me.

I'm using IFTTT.com to automatically append a Google Sheet if I save an article in my RSS reader.

With how IFTTT works, it adds a row to the bottom of a sheet based on various attributes of the article saved.

I'm getting hung up on adding an automatic non-volatile timestamp to the row.

I've tried using the automatic timestamping function below, which works when I type into a cell, but not when the sheet is appended.

function onEdit(event)
{ 
  var timezone = "GMT-5";
  var timestamp_format = "MM-dd-yyyy"; // Timestamp Format. 
  var updateColName = "Article Title";
  var timeStampColName = "Date Discovered";
  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(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

IFTTT lets you set what data gets ported into what column, and has specific "ingredients" for data from Feedly you can put into a column.

They also allow for static text, and formulas.

Realizing this, I found the following custom function, which seems to somewhat work.

function timestamp() {
return new Date()
}

This allowed me to add a function to check whether the title of the article had content, and timestamp if true.

=Timestamp(INDIRECT("E"&ROW()))

Oddly, the first row with this formula did not change when I reloaded the sheet today (I entered the formula yesterday), nor did a row further down the sheet.

Source sheet: https://docs.google.com/spreadsheets/d/1ybfgOgBkqHdld9_sexzMfdMV2lT6Qp7WgQ8Di96S-a8/edit?usp=sharing

Many thanks ahead of time.

Look at an OnEdit or OnSubmit {Event Object}(developers.google.com/apps-script/guides/triggers/events) to maybe run the function and place the timestamp in the proper location. This would be a function of what Trigger IFTTT causes, if any. If an OnEdit then you can get the row from the data passed to the trigger. Other triggers may rquire looping through the spreadsheet to find the last row. — Karl_S

Jack Brown Jack Brown · Accepted Answer · 2017-03-18T00:51:48

Since you are only interested in the date of discovery and not too worried about the exact time. You can set up a time trigger for a function to run daily after midnight to add timestamps of yesterday's date to row with non-empty "article title" column.

Here is code that does just that, also added a function to setup the daily trigger

// Run this function to setup daily trigger to run at midnight to set the date of discovery.

function IntializeTrig(){

 ScriptApp.newTrigger("addTimeStamp").timeBased().atHour(0).everyDays(1).create()
  // If you have issues with timezone or if you work late at night you can change time to say 2 am by changing to this ".atHour(2)"
}
// This function checks to see if the column Date Discovered is empty and add yesterday date to it, if Article Title column is a Non empty String
function addTimeStamp(){
 var ss = SpreadsheetApp.getActive()
 var sheet = ss.getSheetByName("Sheet1") 
 var updateColName = "Article Title";
 var timeStampColName = "Date Discovered";
 var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
 var dateCol = headers[0].indexOf(timeStampColName), dateCol =dateCol + 1;
 var articleCol = headers[0].indexOf(updateColName), articleCol = articleCol + 1;
 var numOfCol
 if (dateCol < articleCol){
     numOfCol = articleCol
 }else {
     numOfCol = dateCol
 }
 var yesterday = new Date(Date.now() - 864e5)
 var yestDate = yesterday.toISOString().slice(0,10)
 var data = sheet.getRange(2, 1,sheet.getLastRow()-1, numOfCol).getValues()
 for (var i = 0 ; i< data.length ; i++){
   if (data[i][articleCol - 1] !== "")
     if (data[i][dateCol - 1] == "")
       data[i][dateCol - 1] = yestDate
 }
  sheet.getRange(2,1,data.length , data[0].length).setValues(data)
}

Hope this helps!

Custom Google Sheet Frozen Timestamp Formula

1 Answers