CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Christiaan Lennaerts
Christiaan Lennaerts

Reputation: 15

Apps script copy multiple range from 1 sheet to another spreadsheet

I am trying to copy data from 1 spreadsheet to another, I have successfully implemented something i found online that works with a specific range

function cloneGoogleSheet() {

  // source doc
  var sss = SpreadsheetApp.openById("spreadsheetkey1");

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

  // Get full range of data
  var SRange = ss.getRange(7,3,5,1);

  // 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("spreadsheetkey2");

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

  // Clear the Google Sheet before copy
  //ts.clear({contentsOnly: true});

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

};

The above piece coding work perfectly however I need to copy 18 different ranges that i cant just merge into 1 range. I considered the option of using the above however "multiplying" it 18 times for each range that however seems like a very inelegant solution.

I found a working solution that works if it stays within the same spreadsheet since it uses copyto instead of get/set values. The values part works perfectly since it doesnt mess with merge cells formatting. I have been struggling past 2-3 hours in merging the below-working code with elements from the first code to make a working script.

function test () {
  try {
    var spread = SpreadsheetApp.openById("spreadsheetkey");
    var sheet = spread.getSheetByName("tab_name_source");
    var rlist = sheet.getRangeList(["c7:c11", "g7:g11", "k7:k11"]);
    sheet = spread.getSheetByName("tab_name_destination");
    for( var i=0; i<rlist.getRanges().length; i++ ) {
      var r1 = rlist.getRanges()[i];
      var r2 = sheet.getRange(r1.getA1Notation());
     
      r1.copyto(r2);
    }
  }
  catch(err) {
    Logger.log(err);
  }
}

I tried initially to adapt the 2nd piece of coding to using setvalues however i had not been able to succesfully implement the part of getvalues within the scope of this code. I figured once I got this piece of code working with get and set values instead of Copyto i would only need to add the spreadsheetid of the other spreadsheet to get the final result

Upvotes: 0

Views: 2388

Answers (1)

Nikko J.
Nikko J.

Reputation: 5533

Try this:

function myFunction() {
  var sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = sourceSS.getSheetByName("sheetname");
  var targetSS = SpreadsheetApp.openById("spreadsheet id here");
  var targetSheet = targetSS.getSheetByName("Sheet1");

  var ranges = ["C7:C11", "G7:G11", "K7:K11"];
  ranges.forEach(range => {
    var data = sourceSheet.getRange(range).getValues();
    targetSheet.getRange(range).setValues(data);
  })
}

Source sheet:

enter image description here

Destination sheet:

enter image description here

References:

Upvotes: 1

Related Questions