I have two spreadsheets, we will call them Active Spreadsheet (where the code is being implemented) and Target Spreadsheet (where the timestamps go) The Active Spreadsheet will have data entered on sheet “Week1” into column D starting at row 4. When the data is entered into column D I want an “Initial Date” timestamp and a “Modified Date” timestamp populated into the Target Spreadsheet, sheet “Week#1” at the corresponding rows in columns 1 and 2 respectively. I have the following code written, and it runs without errors. However, when I input data into column D of the Active Spreadsheet, nothing populates in the Target Spreadsheet. I have tried running the code and researching various reasons as to why it is not working but cannot find a solution.
function onEdit(e){
const row = e.range.getRow();
const col = e.range.getColumn();
const sheetName = "Week1";
if (col === 4 && row > 3 && e.source.getActiveSheet().getName() === "Week1"){
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var targetSpreadsheetId = "TargetSpreadsheetID";
var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
var targetSheet = targetSpreadsheet.getSheetByName("Week#1");
var targetRow = range.getRow();
var timestamp = new Date();
var formattedTimestamp = Utilities.formatDate(timestamp, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
targetSheet.getRange(row,2).setValue(formattedTimestamp);
if(targetSheet.getRange(row,1).getValue() == ""){
targetSheet.getRange(row,1).setValue(formattedTimestamp);
}
}
}
Brian Mueller is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
SUGGESTED SOLUTION
Note: It seems like onEdit(e) won’t work for this because accessing another spreadsheet requires authorization, and as per Restrictions:
They can modify the file they are bound to, but cannot access other files because that would require authorization.
Instead, use Manage triggers manually to run the function. Click on Triggers > + Add Trigger on the left of the Google Apps Script Editor.
SAMPLE TRIGGER IMAGE
I changed the name of the function from
onEdit(e)tomyFunctionso that the triggers will not run twice. This is an example of what it’ll look like if that is not changed:
The converted code would become:
function myFunction() {
// Variables from the original code
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var targetSpreadsheetId = "TargetSpreadsheetID";
var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
var targetSheet = targetSpreadsheet.getSheetByName("Week#1");
var timestamp = new Date();
var formattedTimestamp = Utilities.formatDate(timestamp, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
// Newly declared variables
var sh = activeSpreadsheet.getSheetByName("Week1");
var rg = sh.getActiveRange();
var row = rg.getRow();
var col = rg.getColumn();
var vl = sh.getRange(row, col).getValue();
// Conditional statement that checks if there's already a timestamp in Columns A and B
if (col == 4 && vl != "" && row > 3) {
var cv = targetSheet.getRange(row, 1).getValue();
if (cv != "") {
targetSheet.getRange(row, 2).setValue(formattedTimestamp);
} else {
targetSheet.getRange(row, 1).setValue(formattedTimestamp);
}
}
}
OUTPUT
1