API Connector Documentation
Print Timestamp
The API Connector add-on for Google Sheets provides an option to include a timestamp in your sheet each time your data refreshes. To access, click on the 'Output options' button and check the 'Add timestamp' box.
Contents
Before You Begin
If you haven't already, click here to install the API Connector add-on from the Google Marketplace.
Overview
If you choose to add a timestamp, the timestamp of each data refresh will appear in the first column of the data output. This will shift all response data over by 1 column, so, if you are appending to existing data, insert a column first to accommodate the new timestamp.
In overwrite mode, the refresh timestamp will always be printed into the first row of data after the headers, while in append mode, it will print into the first empty row.
Timestamps can be added to any request, whether scheduled or manually refreshed.
"Each Row" Option
By default, the timestamp will be printed at the start of the data set, meaning there will be empty cells if the response spans multiple rows. If you prefer to see the timestamp on every row, click the "Each row?" option to enable it.
Timezone & Locale
Timestamps will use the timezone of the sheet, which can be viewed by clicking File > Spreadsheet settings. They will match the default format of your locale (also viewable in your spreadsheet settings), though you can change this via date formatting options.
Troubleshooting
If you are running a scheduled refresh, and notice missing or delayed timestamps, please consult this article: Troubleshooting Triggers
Currently timestamp is added only into the first row of imported data.
If someone needs to put timestamp into each data row, i.e. to plot them:
1. Add the following script to your GSheet:
function fillMyRow() {const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1');
const values = sh.getRange('A2:A'+sh.getLastRow()).getValues().flat([1]);
var toCopy;
values.forEach((el,index)=>{
if (el != ''){
toCopy = sh.getRange(index+2,1).getValue();
} else {
sh.getRange(index+2,1).setValue(toCopy)
}
})
}
2. Run this script through time-based trigger, so you get everyday consistent timestamps for your data.
Awesome, thank you for sharing.
This is now available as a native feature using the "Each row" option.