Copy Data from One Google sheets Workbook Sheet to another Workbook

Question

So very much piecing together and learning google scripts and 1st challenge is to do the following: Several subsheets completed by agents who will use a script to collate onto a master sheet any that are not already on there.

Script will need to: Check if data in the subsheet table has been been already copied to master sheet (look at a cell for a 0 or 1). If 0 copy that to the last row of the master sheet. I've used a countifs formula to populate the 0 or 1 on each sub sheet.

I've managed to build the script to do this from one sheet to another in the same workbook but get an error message when trying to do so in different workbooks. Also found a clone script that does copy the data (whole page) to the master but overwrites and not last row and also ignores the 0 and 1 rule.

Looked across many similar questions and online and struggling to see anyone with this same need and fix?

Codes: Copy all the new data to another sheet in same workbook from the last row: /**

Retrieves all the rows in the active spreadsheet that contain Yes
in the Include column and copies them to the Report sheet. */

function copyRows2() {

var sSheet = SpreadsheetApp.getActiveSpreadsheet();

var srcSheet = sSheet.getSheetByName("Import2");

var tarSheet = sSheet.getSheetByName("Report");

var lastRow = srcSheet.getLastRow();

for (var i = 2; i <= lastRow; i++) { var cell = srcSheet.getRange("C" + i); var val = cell.getValue(); if (val == "0") {

var srcRange = srcSheet.getRange("A" + i + ":D" + i);

var tarRow = tarSheet.getLastRow(); var tarRange = tarSheet.getRange("A" + (tarRow+1) + ":D" + (tarRow+1));

srcRange.setValues(tarRange); } } };`

Clone whole Sheet from workbook A to workbook B:

function cloneGoogleSheet(ssA, ssB) {



 // source doc
  var sss = SpreadsheetApp.openById('Source Workbook ID');



// source sheet
  var ss = sss.getSheetByName('Import');

  // Get full range of data
  var SRange = ss.getDataRange();

  // get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();

  // get the data values in range
  var SData = SRange.getValues();

  // target spreadsheet
  var tss = SpreadsheetApp.openById('Target WorkbookID');

  // target sheet
  var ts = tss.getSheetByName('Master2');

  
  // set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);

};

The openById needs the unique identifier of the file. You can get that by Google Drive -> Get link and pulling out the mish mash of characters in the link. For example "docs.google.com/spreadsheets/d/1jflaksjflskjfslfjsfja.../edit?usp=sharing" — TheWizEd
Thanks, the openById worked and I have the links in there but just edited them out of the post. — KomoneAD
The clone works - taking source data and setting it to the new workbook but not what I need. I need the functionality of the 1st code to copy new data to the last row of the target sheet - but for the target sheet to be on another workbook. Cant seem to get that to work. — KomoneAD
So you are saying a copy of data from workbook to workbook works properly. But you want a modified version of data from one workbook to the other? — TheWizEd

TheWizEd TheWizEd · Accepted Answer · 2022-01-26T16:36:24

function cloneGoogleSheet(ssA, ssB) {

 // source doc
  var sss = SpreadsheetApp.openById('Source Workbook ID');

// source sheet
  var ss = sss.getSheetByName('Import');

  // Get full range of data
  var SRange = ss.getDataRange();

  // get the data values in range
  var SData = SRange.getValues();

  var i = 0;
  var TData = [];
  for( i=0; i<SData.length; i++ ) {
    if( SData[i][2] === 0 ) { // Column C = 0
      TData.push(SData[i]);
    }
  }

  // target spreadsheet
  var tss = SpreadsheetApp.openById('Target WorkbookID');

  // target sheet
  var ts = tss.getSheetByName('Master2');

  
  // set the target range to the values of the source data
  ts.getRange(1,1,TData.length,TData[0].length).setValues(TData);  // Over write
  // Append ts.getRange(ts.getLastRow()+1,1,TData.length,TData[0].length).setValues(TData);

};

Copy Data from One Google sheets Workbook Sheet to another Workbook

1 Answers