Google-sheets – Add date stamp when new row added by Zapier

google sheetsgoogle-apps-scriptzapier

I am trying to add a submission date to column 1 whenever a new row is automatically added through Zapier. Is this possible? E.g.,

 C1                              C2              C3
 (DATE STAMP WHEN SUBMITTED)     John            Smith
 (DATE STAMP WHEN SUBMITTED)     Sam             Hardy

Best Answer

If you want to include the time your Zap runs in your Zap (or use that as an approximation of when the trigger occurs), you can use the following field in your Zap: {{zap_meta_human_now}}

Just enter that text with the curly braces into the field instead of selecting something from the dropdown.

For more information, see: https://zapier.com/help/advanced-tips/#time-stamps

Code

function onEdit(e) {
  var aCell = e.source.getActiveCell(), col = aCell.getColumn(); 
  if(col == 2 || col == 4 || col == 6) {
    var adjacentCell = aCell.offset(0, 1);  
    var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy kk:mm:ss");
    adjacentCell.setValue(newDate);
  }
}

Example

I've created an example file for you: Adjacent Cell
Add the code to the script editor (Tools > Script editor...), press the save button and you're on the go !!

Google-sheets – Add a ‘creation date’ value when a new line is edited

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted)
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

I have set up an example spreadsheet, feel free to play with it.

Best Answer

Related Solutions

Google Sheets – Auto Date Added to Adjacent Cells When Data is Entered

Code

Example

Google-sheets – Add a ‘creation date’ value when a new line is edited

Related Topic