App Script Count Spreadsheet Changes, Logged in Another Spreadsheet-CodePudding

I am trying to count all of the changes made in the whole spreadsheet and have a counter held within another spreadsheet, so a time motion study can take place.

I can only get the counter to work when I run the script, but not in the spreadsheet. This set-up works in other spreadsheets where I am checking for changes and noting it in the same spreadsheet, but this is not working for some reason. Anyone notice what I have done wrong?

function onEdit() {   
//spreadsheet to search for changes
var ss =SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var activeCell = sheet.getActiveCell();
var col        = activeCell.getColumn();
var row        = activeCell.getRow();


if(col >=1 && row >=1) {
//Spreadsheet to log the count
var ss1 = SpreadsheetApp.openById('[Spreadsheet ID]');
var sheet1 = ss1.getSheetByName('Sheet2');
var data = sheet1.getRange(2,1).getValue();
var val = data  1;

sheet1.getRange(2,1).setValue(val);

 }
};

CodePudding user response：

I think this will fit your needs.

function onEit(e){
  const timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:MM:SS');
  const sheetname = e.source.getSheetName();
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const oldValue = e.oldValue;
  const value = e.value;

  const ss = SpreadsheetApp.openById('xx')
  const sheet = ss.getSheetByName('Sheet2')
  sheet.appendRow([timestamp, sheetname, row, col, oldValue, value])
}

CodePudding user response：

Thanks @J. G. It seems that the problem is that these triggers cannot go into other files. If anyone out there knows how this could be done, please let me know, as it will save a lot of effort.