COPYING DATA FROM ONE SPREADSHEET TO ANOTHER

Question

I have three source files - Alpha, Beta, Kappa. They have same headers and basically, I just want them all consolidated in one file on a weekly basis. I started with Kappa and somehow it works - it creates a new spreadsheet and copies info from the Kappa file and paste it to the newly created spreadsheet as values.

Now I tried, adding the Alpha file to the same spreadsheet, just below the info that I copied from the Kappa file using the getLastRow function. I got an error saying that source range and target range must be on the same spreadsheet.

function RawExtractCopy() {
  var version = 'ver. 4.01'
  var ssnew = SpreadsheetApp.create('V5 Raw Extract '+ version );             //create a spreadsheet
  var ssnewsheet = ssnew.getSheetByName('Sheet1');                            //get the sheet named Sheet1
  ssnewsheet.insertRows(1,30000);                                             //inserting more rows
  ssnewsheet.setName('V5 Raw Extract');                                       //rename the sheet name of the newly created spreadsheet

  var ssKappa = SpreadsheetApp.getActiveSpreadsheet();                        //opens source file
  var targetss = ssnew;                                                       //define created ss as the target ss

  var srcSheetKappa = ssKappa.getSheetByName('KAPPA');                             //get source sheet name  
  var targetSheet = targetss.getSheetByName('V5 Raw Extract');                //defining target sheet

  var srcRangeKappa = srcSheetKappa.getRange("A1:AM30000");                   //get source data
  var destRangeKappa = targetss.getRange("A1");                            //define target     

  var values = srcRangeKappa.getValues();                                     //line 18 to 21 is just to match source sheet and target sheet
  var bGcolors = srcRangeKappa.getBackgrounds();                              
  var colors = srcRangeKappa.getFontColors();
  var fontSizes = srcRangeKappa.getFontSizes();

  destRangeKappa.setValues(values);
  destRangeKappa.setBackgrounds(bGcolors);
  destRangeKappa.setFontColors(colors);
  destRangeKappa.setFontSizes(fontSizes);

  srcRangeKappa.copyTo(destRangeKappa, {contentsOnly: true});

  var ssAlpha = SpreadsheetApp.openById("1gIs4vCdcGG79poDujz9t8Fq7_BWlaEiMrYUH9DTxVHs").activate();
  var srcSheetAlpha = ssAlpha.getSheetByName('V5 ALPHA');
  var srcRangeAlpha = srcSheetAlpha.getRange("A2:AM30000");
  var destRangeAlpha = targetSheet.getRange(targetSheet.getLastRow()+1,1);

  var valuesAlpha = srcRangeAlpha.getValues();
  var bGcolorsAlpha = srcRangeAlpha.getBackgrounds();                         
  var colorsAlpha = srcRangeAlpha.getFontColors();
  var fontSizesAlpha = srcRangeAlpha.getFontSizes();

  destRangeAlpha.setValues(values);
  destRangeAlpha.setBackgrounds(bGcolors);
  destRangeAlpha.setFontColors(colors);
  destRangeAlpha.setFontSizes(fontSizes);

  srcRangeKappa.copyTo(destRangeAlpha, {contentsOnly: true});

}

The thing, it still copies the data from Kappa but did not push through with Beta. Can someone please be kind to tell what I am missing here?

Answer 1

The OP's goal is to make a duplicate of three sheets in three separate spreadsheets; Alpha, Beta and Kappa. The OP's code works for Kappa, but not Alpha or Beta.

Why does this code work and the OP code doesn't
Well, the OP code does work, but the OP fudges by specifying a fixed range (30000 rows long and about 40 column wide). This works for the first sheet, but not so easily for the second and third sheets. The following code differs in two major respects from the OP code:

1. the data ranges in Alpha, Beta, Kappa and "Raw Extract" are evaluated using getLastRow and getLastColumn. So the source range and the target range will match each other. In addition, data from the second and third sheets is easily appended one row below the preceding last row.
2. the copyTo code is changed to copy the whole sheet rather than a range. The reason for this is the the preceding four commands setValues(values), setBackgrounds(bGcolors), setFontColors(colors), and setFontSizes(fontSizes) have already copied the data range to "Raw Extract".

---

function so_55448299() {

  var version = 'ver. 4.01'
  var ssnew = SpreadsheetApp.create('V5 Raw Extract ' + version); //create a spreadsheet
  var ssnewsheet = ssnew.getSheetByName('Sheet1'); //get the sheet named Sheet1
  ssnewsheet.setName('V5 Raw Extract'); //rename the sheet name of the newly created spreadsheet

  // copy Kappa and data
  var ssKappa = SpreadsheetApp.getActiveSpreadsheet(); //opens source file
  var targetss = ssnew; //define created ss as the target ss
  var srcSheetKappa = ssKappa.getSheetByName('KAPPA'); //get source sheet name  
  var targetSheet = targetss.getSheetByName('V5 Raw Extract'); //defining target sheet

  // get the last row and column of the source sheet
  var kappaLastRow = srcSheetKappa.getLastRow();
  var kappaLastCol = srcSheetKappa.getLastColumn();
  //Logger.log("DEBUG: Kappa Last row = "+kappaLastRow+", Last column = "+kappaLastCol);//DEBUG

  // declare the source and target ranges
  var srcRangeKappa = srcSheetKappa.getRange(1, 1, kappaLastRow, kappaLastCol); //get source data
  var destRangeKappa = targetSheet.getRange(1, 1, kappaLastRow, kappaLastCol); //define target     

  // get values and other data
  var values = srcRangeKappa.getValues(); //line 18 to 21 is just to match source sheet and target sheet
  var bGcolors = srcRangeKappa.getBackgrounds();
  var colors = srcRangeKappa.getFontColors();
  var fontSizes = srcRangeKappa.getFontSizes();

  // set values and other data  
  destRangeKappa.setValues(values);
  destRangeKappa.setBackgrounds(bGcolors);
  destRangeKappa.setFontColors(colors);
  destRangeKappa.setFontSizes(fontSizes);

  // duplicate the entire sheet
  srcSheetKappa.copyTo(targetss);
  // end copy Kappa and data


  // start copy Alpha and data
  var ssAlpha = SpreadsheetApp.openById("<insert code here>");
  var srcSheetAlpha = ssAlpha.getSheetByName('V5 ALPHA');

  // get the last row and column of the source sheet
  var alphaLastRow = srcSheetAlpha.getLastRow();
  var alphaLastCol = srcSheetAlpha.getLastColumn();
  //Logger.log("DEBUG: Alpha Last row = "+alphaLastRow+", Alpha Last Column = "+alphaLastCol);//DEBUG

  // get the last row and column of the target sheet
  var rawLastRow = targetSheet.getLastRow();
  var rawLastCol = targetSheet.getLastColumn();
  Logger.log("DEBUG: Target Last row = " + rawLastRow + ", Target Last Column = " + rawLastCol); //DEBUG

  // declare the source and target ranges
  var srcRangeAlpha = srcSheetAlpha.getRange(2, 1, alphaLastRow - 1, alphaLastCol);
  var destRangeAlpha = targetSheet.getRange(rawLastRow + 1, 1, alphaLastRow - 1, alphaLastCol);
  //Logger.log("DEBUG:destRangeAlpha =  "+destRangeAlpha.getA1Notation());//DEBUG

  // get values and other data
  var values = srcRangeAlpha.getValues();
  var bGcolors = srcRangeAlpha.getBackgrounds();
  var colors = srcRangeAlpha.getFontColors();
  var fontSizes = srcRangeAlpha.getFontSizes();

  // set values and other data  
  destRangeAlpha.setValues(values);
  destRangeAlpha.setBackgrounds(bGcolors);
  destRangeAlpha.setFontColors(colors);
  destRangeAlpha.setFontSizes(fontSizes);

  // duplicate the entire sheet
  srcSheetAlpha.copyTo(targetss);
  // end copy Alpha and data


  // start copy Beta and data
  var ssBeta = SpreadsheetApp.openById("<insert code here>");
  var srcSheetBeta = ssBeta.getSheetByName('V5 BETA');

  // get the last row and column of the source sheet
  var betaLastRow = srcSheetBeta.getLastRow();
  var betaLastCol = srcSheetBeta.getLastColumn();
  //Logger.log("DEBUG: Beta Last row = "+betaLastRow+", Beta Last Column = "+betaLastCol);//DEBUG

  // get the last row and column of the target sheet
  var rawLastRow = targetSheet.getLastRow();
  var rawLastCol = targetSheet.getLastColumn();
  //Logger.log("DEBUG: Target Last row = "+rawLastRow+", Target Last Column = "+rawLastCol);//DEBUG

  // declare the source and target ranges
  var srcRangeBeta = srcSheetBeta.getRange(2, 1, betaLastRow - 1, betaLastCol);
  var destRangeBeta = targetSheet.getRange(rawLastRow + 1, 1, betaLastRow - 1, betaLastCol);
  //Logger.log("DEBUG:destRangeBeta =  "+destRangeBeta.getA1Notation());//DEBUG

  // get values and other data
  var values = srcRangeBeta.getValues();
  var bGcolors = srcRangeBeta.getBackgrounds();
  var colors = srcRangeBeta.getFontColors();
  var fontSizes = srcRangeBeta.getFontSizes();

  // set values and other data  
  destRangeBeta.setValues(values);
  destRangeBeta.setBackgrounds(bGcolors);
  destRangeBeta.setFontColors(colors);
  destRangeBeta.setFontSizes(fontSizes);

  // duplicate the entire sheet
  srcSheetBeta.copyTo(targetss);
  // end copy Beta and data
}

---

UPDATE - One Small Function

@tehhowch rightly observes that a more appropriate way to manage this process would be one small function called with a few parameters from a driver function. This is something that I had in mind at the time but (for better or worse) I felt that the long-hand approach would enable the OP to better understand how the code varied from their own. However, this variation seeks to fulfil tehhowch's observation. I have left in the Logger statements for the benefit of the OP (which explains the extraordinary length of the code

function so_55448299_04() {

  //Note#1: this function assumes that it is located in the "Kappa" sheet
  //Note#2: the spreadsheet ID for Alpha and Beta cannot be assigned to a variable. they must be entered longhand BEFORE this function is processed.
  //Note#3: the target sheet names are also entered longhand. BUT the number of sheets is described in the variable "usersheets"

  // user defined variables
  var targetVn = 'ver. 4.01';
  var targetName = 'V5 Raw Extract';
  var usersheets = 3;

  // create the targetspreadsheet and starting sheet
  var targetss = SpreadsheetApp.create(targetName + " " + targetVn);
  var targetSheet = targetss.getSheetByName('Sheet1');
  targetSheet.setName(targetName);

  //loop through the usersheets
  for (var i = 0; i < usersheets; i++) {

    // if i==0, then process this sheet - KAPPA
    if (i == 0) {
      //Logger.log("DEBUG: This is KAPPA");//DEBUG
      var srcss = SpreadsheetApp.getActiveSpreadsheet();
      //Logger.log("DEBUG: this spreadsheet is "+srcss.getName());//DEBUG

      // startrow is 1 in order to include headers
      var startrow = 1;
      var srcSheet = srcss.getSheetByName("v5 KAPPA");
    }

    // if i=1, then process Alpha
    else if (i == 1) {
      //Logger.log("DEBUG: This is ALPHA");//DEBUG
      var srcss = SpreadsheetApp.openById("<Insert code>");
      //Logger.log("DEBUG: this spreadsheet is "+srcss.getName());//DEBUG

      // startrow is 2 in iorder to avoid duplicating headers
      var startrow = 2;
      var srcSheet = srcss.getSheetByName("v5 ALPHA");
    }
    // if i=2, then process Beta
    else if (i == 2) {
      //Logger.log("DEBUG: This is BETA");//DEBUG
      var srcss = SpreadsheetApp.openById("<Insert code>");
      //Logger.log("DEBUG: this spreadsheet is "+srcss.getName());//DEBUG

      // startrow is 2 in iorder to avoid duplicating headers
      var startrow = 2;
      var srcSheet = srcss.getSheetByName(usersheets[i]);
    }

    // run the subroutine to copy and paste data
    // Logger.log("DEBUG: srcSheet: "+srcSheet.getName()+", targetSheet: "+targetSheet.getName()+", startrow: "+startrow+", targetss: "+targetss.getName());//DEBUG
    var getresult = getData04(srcSheet, targetSheet, startrow, targetss);
  }

}

function getData04(srcSheet, targetSheet, startrow, targetss) {

  //get the source sheet - last row and column
  var srcLastRow = srcSheet.getLastRow();
  var srcLastCol = srcSheet.getLastColumn();
  //Logger.log("DEBUG: Source Last row = "+srcLastRow+", Last column = "+srcLastCol);//DEBUG

  // get the target sheet - last row and column 
  var targetLastRow = targetSheet.getLastRow();
  var targetLastCol = targetSheet.getLastColumn();
  //Logger.log("DEBUG: Target Last row = "+targetLastRow+", Target Last Column = "+targetLastCol);//DEBUG  

  // declare the source and target ranges
  if (startrow == 1) {
    var srcRange = srcSheet.getRange(startrow, 1, srcLastRow, srcLastCol);
    var targetRange = targetSheet.getRange(startrow, 1, srcLastRow, srcLastCol);
  } else {
    var srcRange = srcSheet.getRange(startrow, 1, srcLastRow - 1, srcLastCol);
    var targetRange = targetSheet.getRange(targetLastRow + 1, 1, srcLastRow - 1, srcLastCol);
  }
  //Logger.log("DEBUG: srcRange = "+srcRange.getA1Notation()+", target range = "+targetRange.getA1Notation());//DEBUG

  // get source values and other data
  var values = srcRange.getValues();
  var bGcolors = srcRange.getBackgrounds();
  var colors = srcRange.getFontColors();
  var fontSizes = srcRange.getFontSizes();

  // set values and other data  
  targetRange.setValues(values);
  targetRange.setBackgrounds(bGcolors);
  targetRange.setFontColors(colors);
  targetRange.setFontSizes(fontSizes);

  // duplicate the entire sheet
  srcSheet.copyTo(targetss);

  var result = "Successful";
  return result;
}