Creating a backup of data entered into a sheet via Google App Scripts

0
votes

I have a spreadsheet where users can enter data and then execute a function when clicking on a button. When the button is clicked it logs the time and entered data in a new row on another sheet in that spreadsheet.

To make sure that sheet is not accidentally edited by the users I want to create a non-shared backup of that data.

I import the range to another spreadsheet, but just importing the range means that if the original sheet is edited/erased that data will also be edited/erased, so I wrote the following script to log the changes as they come in.

    function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var incomingSheet = ss.getSheetByName('Incoming');
var lastRow = incomingSheet.getLastRow();
var incomingData = incomingSheet.getRange(lastRow,1,1,7);

var permanentSheet = ss.getSheetByName('PermanentLog')
var newdataRow = permanentSheet.getLastRow();

incomingData.copyTo(permanentSheet.getRange(newdataRow+1,1));
}

This works when Run from the Apps Script Editor, however, when I enter new data and click the button on the original spreadsheet, it logs the data to the log sheet there, and the range is imported to the 'Incoming' sheet of the new Spreadsheet, but the data is not copied over to the 'Permanent Log' sheet (unless I Run it manually from within the Apps Script Editor). It also works if I remove the ImportRange function from the first sheet and then just manually enter data in on the 'Incoming' sheet.

So does this mean new rows from an Imported Range do not trigger onEdit? What would be the solution? I don't want to run this on a timed trigger, I want to permanently capture each new row of data as it comes in.

Also, am I overlooking a more elegant and simple solution to this whole problem?

Thank you for your time.

2
google-apps-scriptgoogle-sheets
Figured out a simpler solution by having the original button click also perform SpreadsheetApp.openById and just appendRow with data directly to the separate spreadsheet. Would still like to know if onEdit can't be triggered by new lines from an ImportRange function though. – campbellsea
One of the problems with your code is that it runs on every edit of every sheet. If you want to simplify it you can write it like this: function onEdit(e){ const sh=e.range.getSheet(); const ash=e.soure.getSheetByName('PermanentLog'); sh.getRange(sh.getLastRow(),1,1,7).copyTo(ash.getRange(ash.getLastRow()+1,1));} but then you can't run it from the script editor unless you pass it the event object. Also you code doesn't access another spreadsheet. – Cooper
Sorry. I should have clarified that the above script exists on the separate spreadsheet that has an 'Incoming' sheet with an IMPORTRANGE function in the first cell so it's duplicating the sheet I want to archive. Since it only imports from the logged sheet on the other spreadsheet and the data is logged as a whole line each time it avoids the problem of onEdit being triggered with each cell entry. . . but failed because onEdit can't be triggered by the ImportRange function. Looking at your solution below. – campbellsea

2 Answers

1
votes

Restrictions

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

https://developers.google.com/apps-script/guides/triggers

So yeah, as far as I understand you it can't be done that way.

1
votes

This function will copy the data to a new Spreadsheet whenever you edit column 7 which I assume is the last column in your data. It only does it for the sheets that you specify in the names array. Note: you cannot run this from the script editor without getting an error unless you provide the event object which replaces the e. I used an installable onEdit trigger.

The function also appends a timestamp and a row number to the beginning of the archive data row

function onMyEdit(e) {
  e.source.toast('entry');//just a toast showing that the function is working for debug purposes
  const sh = e.range.getSheet();//active sheet name
  const names = ['Sheet1', 'Sheet2'];//sheetname this function operates in
  if (~names.indexOf(sh.getName()) && e.range.columnStart == 7) {
    const ass = SpreadsheetApp.openById('ssid');//archive spreadsheet
    const ash = ass.getSheetByName('PermanentLog');//archive sheet
    let row = sh.getRange(e.range.rowStart, 1, 1, 7).getValues()[0];
    let ts = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy/MM/dd HH:mm:ss");//timestamp
    row.unshift(ts, e.range.rowStart);//add timestamp and row number to beginning
    Logger.log(row);//logs the row for debug purposes
    ash.appendRow(row);//appends row to bottom of data with ts and row

  }
  Logger.log(JSON.stringify(e));
}