CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Benson Batty
Benson Batty

Reputation: 15

Google Script how to back up new rows to a master spreadsheet

So the gist of my project is to copy new data added to spreadsheet A to spreadsheet B. The intention is to use spreadsheet A as an input method and spreadsheet B as a log of all data input into A from all of time. A master sheet so to speak. Note that this will be run on a time-based trigger and all data from sheet a will be deleted.

I've taken some code from Stackoverflow (thank you to who made it) and made some changes. I'm able to copy over the first set of data from A to B but when I delete the data from A, add new data and run the code I lose all I've input in B. I may as well have use =importrange.

My solution was logical and to take the getLastRow of B and put this before setting setValues. Theoretically, this should get the last row of B and input the new data from A into the last row of B. Easy right, NO, I've been trying to figure this out for days and many hours reading similar cases but I'm not able to get past this hurdle.

I'm speculating that I've misunderstood something in my variable SRange, A1Range or SData.

Any help would be super helpful.

function CopyDataToNewSheet() {
  var sss = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXX');
  var ss = sss.getSheetByName('Sheet1'); 
  var lr = ss.getLastRow();
  var lc = ss.getLastColumn();
  var SRange = ss.getRange(2,1,lr,lc);
  var A1Range = SRange.getA1Notation();
  var SData = SRange.getValues();
  var tss = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXX');
  var ts = tss.getSheetByName('Sheet1'); 
  var tlr = ts.getLastRow()+1;
  Logger.log(tlr);
  tlr.getRange(A1Range).setValues(SData);
}

Upvotes: 0

Views: 93

Answers (1)

Cooper
Cooper

Reputation: 64042

function CopyDataToNewSheet() {
  var ss=SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXX');
  var sh=ss.getSheetByName('Sheet1'); 
  var srg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var dA=srg.getValues();
  var tss=SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXX');
  var tsh=tss.getSheetByName('Sheet1'); 
  tsh.getRange(tsh.getLastRow()+1,1,dA.length,dA[0].length).setValues(dA);
}

Upvotes: 1

Related Questions