Export Range of Data from One Google Spreadsheet to Another using Google Apps Script

Question

I have two different Google Spreadsheets with two different SheetIDs (Spreadsheet_Source and Spreadsheet_Target). I am looking to copy a Range("B3:AE55") from Spreadsheet_Source to Range("B3:AE55") in Spreadsheet_Target. I used a for loop to get majority of the formatting, however, the code takes over a minute to execute.

Is there an easier way to copy the data AND its formatting from Spreadsheet_Source to Spreadsheet_Target WITHOUT using the copyTo() function?

Current Apps Script Code:

function exportOrderData() {
  var sourceSpreadsheetID = "";
  var sourceWorksheetName = "AddOrders";
  var targetSpreadsheetID = "";
  var targetWorksheetName = "CopyToTest";

  // The "AddOrders" Child Sheet from the "SignDreamersInventory" Parent Sheet
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var sourceWorksheet = sourceSpreadsheet.getSheetByName(sourceWorksheetName);
  var sourceData = sourceWorksheet.getRange("B3:D55").activate();               // Gets Order #1 Data

  // The "CopyToTest" Child Sheet from the "CopyToTest" Parent Sheet
  var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
  var targetWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
  var targetRange = targetWorksheet.getRange(1, 1, sourceData.getNumRows(), sourceData.getNumColumns());
  
  var rowStart = 3;
  var columnStart = 2;
  var backgroundColor = "";
  var kitSelectRowStarts = 9;
  var kitSelectRowEnds = 10;

  sourceNumberOfColumns = 30;   // 30 Columns holding the Order Information
  sourceNumberOfRows = 52;      // 55 Rows holding the Order Information

  for(var i = 0; i <= 30; i++){
    for(var j = 0; j <= 52; j++){
      

      backgroundColor = sourceWorksheet.getRange(rowStart + i, columnStart + j).getBackground();
      formula = sourceWorksheet.getRange(rowStart + i, columnStart + j).getFormula();
      text = sourceWorksheet.getRange(rowStart + i, columnStart + j).getValue();
      fontWeight = sourceWorksheet.getRange(rowStart + i, columnStart + j).getFontWeight();
      fontSize = sourceWorksheet.getRange(rowStart + i, columnStart + j).getFontSize();
      fontColor = sourceWorksheet.getRange(rowStart + i, columnStart + j).getFontColor();
      textHorAlignment = sourceWorksheet.getRange(rowStart + i, columnStart + j).getHorizontalAlignment();
      textVerAlignment = sourceWorksheet.getRange(rowStart + i, columnStart + j).getVerticalAlignment();

      //Logger.log(wrap[0][0]);

      // Override and set background, font weight, wrap, etc.
      // targetWorksheet.getRange(rowStart + i, columnStart + j).setBackground(backgroundColor)
      
      // WRAP only the Kit Rows
      if(i == 6 || i == 7){
        targetWorksheet.getRange(rowStart + i, columnStart + j).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
      }
      else{
        targetWorksheet.getRange(rowStart + i, columnStart + j).setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
      }
      targetWorksheet.getRange(rowStart + i, columnStart + j)
        .setValue(text)
        .setFontWeight(fontWeight)
        .setFontSize(fontSize)
        .setFontColor(fontColor)
        .setHorizontalAlignment(textHorAlignment)
        .setVerticalAlignment(textVerAlignment);
    }
  }
  


}

I think you can use Sheet.copyTo(Spreadsheet) and then range.copyto(range) the later required to be in same spreadsheet and then you can delete the copied sheet. Probably less than ten lines of code — Cooper

Cooper Cooper · Accepted Answer · 2020-12-18T06:48:34

Try this:

function exportOrderData() {
  const sss=SpreadsheetApp.openById("ssid");//src
  const ssh=sss.getSheetByName("AddOrders");
  const tss=SpreadsheetApp.openById('ssid');//tgt
  const tsh=tss.getSheetByName("CopyToTest")
  const nsh=ssh.copyTo(tss);
  nsh.getRange("B3:D55").copyTo(tsh.getRange("A1"));
  tss.deleteSheet(nsh);
}

Sheet.copyTo(Spreadsheet)

range.copy(range)

Export Range of Data from One Google Spreadsheet to Another using Google Apps Script

1 Answers