CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
campbellsea
campbellsea

Reputation: 58

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

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.

Upvotes: 0

Views: 552

Answers (2)

Cooper
Cooper

Reputation: 64110

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));
}

Upvotes: 1

Yuri Khristich
Yuri Khristich

Reputation: 14537

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.

Upvotes: 1

Related Questions