CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetstriggerstimestamp
sed
sed

Reputation: 1

Google sheet separate log page with sellected columns script

I have a Google Sheet problem. My main page (which name is "MAIN", collects its data from other sheets. So, 'MAIN' sheet looks like this: MAIN

I want to create a separate log page, which collects only "MAIN" sheets data when ever somebody change his hotel, next to his name. (Hotel column is 4th on the MAIN sheet)

..So "LogSheet" needs to collect 1st second and 4th column with timestamp..

so at the end there will be a list (who went, which hotell and when). it needs to be like this, LogSheet but I couldnt write it :/

function onEdit(e) {
  
  var sheetsToWatch = ["MAIN"];
  var changelogSheetName = ["LogSheet"];

  var timestamp = new Date();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var sheetName = sheet.getName();
  
  if(col === 4 && row > 1 && e.source.sheetsToWatch() .getName() === "MAIN" ){
  e.changelogSheetName().getRange('MAIN', row,1).setValue(new Date());
  }   

 }

Upvotes: 0

Views: 54

Answers (1)

ziganotschka
ziganotschka

Reputation: 26806

There is some confusion about the usage of event objects and variables

  1. Variables (including arrays) are called without () - () are reserved for calls of functions
  2. If your variable cntains only one value, not an array - there is no need to put it in [] brackets. [] brackets designate a variable as an array and it needs to be proceeded differently.
  3. To verify either a sheet is contained in an array of sheetnames, you need to use e.g. indexOf() instead of ==
  4. e.source returns you the spreadsheet in which the edit has taken place. When querying for e.source.getActiveSheet() you will obtain in most situations correctly the sheet in which the edit took place, but to account for exceptions it is better to use the event object range together with the method https://developers.google.com/apps-script/reference/spreadsheet/range#getsheet
  5. The methodgetRange() expects rows, columns and optionally row and column numbers - not sheet names as variables.

Please have a look at the modifications in the following samples.

Sample with a sheet array

function onEdit(e) {
  
  var sheetsToWatch = ["MAIN", "add another sheet if desired" ];
  var changelogSheetName = "LogSheet";
  var logSheet = SpreadsheetApp.getActive().getSheetByName(changelogSheetName);
  var timestamp = new Date();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var sheet = e.range.getSheet();
  var sheetName = sheet.getName();
  Logger.log(e.range.getSheet().getName());
  if(col === 4 && row > 1 && sheetsToWatch.indexOf(sheetName)!=-1){
    logSheet.getRange(row,1).setValue(new Date());
    //do whatever else you want
  }     
}

Sample with a single sheet as a variable

function onEdit(e) {
  
  var sheetsToWatch = "MAIN";
  var changelogSheetName = "LogSheet";
  var logSheet = SpreadsheetApp.getActive().getSheetByName(changelogSheetName);
  var timestamp = new Date();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var sheet = e.range.getSheet();
  var sheetName = sheet.getName();
  Logger.log(e.range.getSheet().getName());
  if(col === 4 && row > 1 && sheetsToWatch ==sheetName){
    logSheet.getRange(row,1).setValue(new Date());
    //do whatever else you want
  }     
}

Please refer to Apps Script documentation and Guides for better understanding.

UPDATE

For your case, the most convenient is to retrieve values from the edited row and append them in the desired order to LogSheet

Sample

function onEdit(e) {
  
  var sheetsToWatch = "MAIN";
  var changelogSheetName = "LogSheet";
  var logSheet = SpreadsheetApp.getActive().getSheetByName(changelogSheetName);
  var timestamp = new Date();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var sheet = e.range.getSheet();
  var sheetName = sheet.getName();
  if(col === 4 && row > 1 && sheetsToWatch ==sheetName){
    var values = sheet.getRange(row,1,1,4).getValues()[0];
    logSheet.appendRow([new Date(), values[0], values[1], values[3]]);
  }     
}

Upvotes: 1

Related Questions