Getting exact date and time by new row in Google Sheets via Zapier-CodePudding

I have a simple but stubborn problem. I have data from our ESP which I send via Zapier into a Google Sheet. Unfortunately, I can not send the SignUp date from the ESP via Zapier, so I am looking for a solution to create the date by a new entry within the Google Sheet.

I've tried =TODAY() and =NOW() inside the row in Zapier, so the function will be put in every single time from Zapier. The problem is, that both of these functions give me every time the time of just right now, so if I have a week-old entry, it still shows me that it is from today.

Next, I've tried to use an array function.

=ARRAYFORMULA(IF(ISBLANK($B4376:$B)=TRUE, "", NOW()))

But I have the same exact problem, the momentary date of the input is not saved but always just right now overwritten.

Does anybody have any idea how I could create a date & time by entry?

Thankful for any ideas!

CodePudding user response：

I've found a script but it only works on manual edit. By input from Zapier the script does not react. I've tried it with doGet instead of onEdit, but I get no results..

var SHEET_NAME = 'Boxes Dog Live Dashboard';
var DATETIME_HEADER = 'date';

function getDatetimeCol(){
  var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
  var colindex = headers.indexOf(DATETIME_HEADER);
  return colindex 1;
}

function doGet(e) {  
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var datecell = ss.getRange(cell.getRowIndex(), getDatetimeCol());
  if (ss.getName() == SHEET_NAME && cell.getColumn() == 2 && !cell.isBlank() && datecell.isBlank()) {      
    datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm");
  }
};

CodePudding user response：

Zapier supports date/time commands in the action fields. All you need to do is add one of these commands when you're setting up the action.

You can use one of the following entries:

{{zap_meta_human_now}}
{{zap_meta_utc_iso}}
{{zap_meta_timestamp}}

There are also entries for all the US timezones

EST: {{zap_meta_est_iso}} ISO-8601 formatted date and time in Eastern Standard Time
EDT: {{zap_meta_edt_iso}} ISO-8601 formatted date and time in Eastern Daylight Time
CST: {{zap_meta_cst_iso}} ISO-8601 formatted date and time in Central Standard Time
CDT: {{zap_meta_cdt_iso}} ISO-8601 formatted date and time in Central Daylight Time
MST: {{zap_meta_mst_iso}} ISO-8601 formatted date and time in Mountain Standard Time
MDT: {{zap_meta_mdt_iso}} ISO-8601 formatted date and time in Mountain Daylight Time
PST: {{zap_meta_pst_iso}} ISO-8601 formatted date and time in Pacific Standard Time PDT: {{zap_meta_pdt_iso}} ISO-8601 formatted date and time in Pacific Daylight Time

For more info see

Destination data (with date/time added by Zap)

Example set-up to add date/time