Can anyone provide assistance with a problem in Google Spreadsheet?
I am looking to log the name and timestamp when a value in a specific column in the "Venues" sheet is changed. However, I'm having trouble determining if I'm working with the correct spreadsheet or not. I am not very familiar with the class structure of Google API for Spreadsheet. Can someone help me with this?
Here's what I need:
- Run an event handler when a value in the appropriate column in the "Venues" sheet is changed
- Retrieve the value from the column with names in this sheet
- Get the current timestamp
- Write the name and timestamp to another sheet named "status_history" in the last row (like append)
This is my attempt at writing something, although I know it's not great:
function onEdit(event)
{
var sheet = event.source.getActiveSheet();
var cell = sheet.getActiveCell();
var cellR = cell.getRow();
var cellC = cell.getColumn();
var cellValue = cell.getValue();
var cellCName = cell.getColumn()-1; //column with names
var name = sheet.getRange(cellR, cellCName).getValue();//get name
var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if(sheet.getName() == "Venues"){
if(cellC == 5 /* if correct collumn was changed */){
var output_sheet = active_spreadsheet.getSheetByName("status_history");
var lastRow = output_sheet.getLastRow();
var lastRange = output_sheet.getRange(lastRow, 1)
//HERE: write value: name
var lastRow = output_sheet.getLastRow();
var lastRange = output_sheet.getRange(lastRow, 2)
//HERE: write value: time
}
}
}