Temporal table Google sheet-CodePudding

i'm working with google sheet as primary database. The data is sent and stored to 2 sheets where the user can interact with it. The user can also modify the data. The target is to track all changes from the 2 sheets and to copy the changes to another sheets. If sheet 1 is modified, the edited row will be copied to HistoricalSheet 1 and same for sheet 2. when the data will be copied, it will be accompanied with a timestamp and the userID, who modified the sheet.

My issue is that when retrieving the row range, it is undefined.

Here is my code:

function onEdit(e){
  var timeStamp = Utilities.formatDate(new Date(), "GMT 8", "hh:mm:ss dd MMMM yyyy")
  var userID = Session.getActiveUser().getUserLoginId();
  var sourceSheet = e.range.getSheet();
  var row = e.range.getRow();
  var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
  var rowValues = rowRange.getValues()[0];

  if(sourceSheet.getSheetName() == 'Sheet1'){
    var targetSheet = SpreadsheetApp.openById('xxxxxxxxxxxxxx').getSheetByName('HistoricalSheet 1');
    targetSheet.appendRow([timeStamp,userID,rowValues]);
  } else if(sourceSheet.getSheetName() == 'Sheet2'){
    var targetSheet = SpreadsheetApp.openById('xxxxxxxxxxxxxx').getSheetByName('HistoricalSheet 2');
    targetSheet.appendRow([timeStamp,userID,rowValues]);
    }
  }

CodePudding user response：

Try this:

function onEdit(e){
  const ts = Utilities.formatDate(new Date(), "GMT 8", "hh:mm:ss dd MMMM yyyy")
  const uid = Session.getActiveUser().getUserLoginId();
  const sh = e.range.getSheet();
  const rg = sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn());
  const vs = rg.getValues()[0];
  if(sh.getSheetName() == 'Sheet1'){
    let tsh = SpreadsheetApp.openById('xxxxxxxxxxxxxx').getSheetByName('HistoricalSheet 1');
    tsh.appendRow([ts,uid,vs]);
  } else if(sh.getSheetName() == 'Sheet2'){
    let targetSheet = SpreadsheetApp.openById('xxxxxxxxxxxxxx').getSheetByName('HistoricalSheet 2');
    targetSheet.appendRow([ts,uid,vs]);
    }
  }

CodePudding user response：

From The onEdit works now based on this link. the issue now that the timestamp, the userid are append but the values is in form of [Ljava.lang.Object;@34c3ed94 in a cell only. The values aren't append to the rest of the row. in your reply, I understood that your issue of My issue is that when retrieving the row range, it is undefined. was resolved.

About your current issue, I think that the reason for your issue is due to rowValues is an array. So in this case, how about the following modification?

Modified script:

function installedOnEdit(e) {
  var timeStamp = Utilities.formatDate(new Date(), "GMT 8", "hh:mm:ss dd MMMM yyyy")
  var userID = Session.getActiveUser().getUserLoginId();
  var sourceSheet = e.range.getSheet();
  var rowRange = sourceSheet.getRange(e.range.rowStart, 1, 1, sourceSheet.getLastColumn());
  var rowValues = rowRange.getValues()[0];
  var obj = { "Sheet1": "HistoricalSheet 1", "Sheet2": "HistoricalSheet 2" };
  var sheetName = sourceSheet.getSheetName();
  if (!obj[sheetName]) return;
  var targetSheet = SpreadsheetApp.openById('xxxxxxxxxxxxxx').getSheetByName(obj[sheetName]);
  targetSheet.appendRow([timeStamp, userID, ...rowValues]); // targetSheet.appendRow([timeStamp, userID, rowValues.join(",")]);
}

In this modification, the edited row is put to the specification sheet in another Spreadsheet using an object for searching the sheet name. By this, I thought that the script can be simpler a little.
And, by targetSheet.appendRow([timeStamp, userID, ...rowValues]), the edited row is put to a row.
If you want to put the row values into one cell, please modify to targetSheet.appendRow([timeStamp, userID, rowValues.join(",")]).

Note:

In this modification, I modified the function name from onEdit to installedOnEdit. Because when SpreadsheetApp.openById is used, it is required to use the installable OnEdit trigger. But, when the installable OnEdit trigger is installed to the function onEdit, onEdit is run by both the simple trigger and the installable trigger with asyncrhonous process. So, I modified the function name. So, please install the installable OnEdit trigger to the function installedOnEdit, and test it again.

Try this:

Modified script:

Note:

References: