Copy Multiple Sheet Values to New Google Spreadsheet File

Other Questions Google Sheets
1

I’m working on a Google Apps Script to help with our student tracking system. Every quarter I need to move several sheets from our main tracking spreadsheet into a separate archive file. The data should be copied as values only, not formulas.

I can make this work for one sheet at a time, but I’m struggling with the loop syntax to process all sheets together. Here’s what I have so far:

function archiveSheetData() {
  var mainFile = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheets = ['points', 'awards', 'violations', 'reports'];
  
  for (var j=0; j<4; j++) {
    var currentSheet = mainFile.getSheetByName(targetSheets[j]);
    currentSheet.activate();
    
    var sheetTitle = currentSheet.getSheetName();
    var dataRange = currentSheet.getDataRange();
    var cellValues = dataRange.getValues();
    var totalRows = dataRange.getNumRows();
    var totalCols = dataRange.getNumColumns();
    
    var newFile = SpreadsheetApp.create('Quarterly Archive');
    var fileUrl = newFile.getUrl();
    var targetFile = SpreadsheetApp.openByUrl(fileUrl);
    
    targetFile.insertSheet(sheetTitle, 0);
    targetFile.getDataRange().offset(0, 0, totalRows, totalCols).setValues(cellValues);
  }
}

The script runs but only processes the last sheet. What am I missing in my loop structure?

2

You’re creating a new archive file every loop iteration, which wipes out your previous sheets. Create the archive file once before the loop starts. Also, you’re calling getDataRange() on the entire target file instead of the specific sheet you just made. Here’s what to fix: move file creation outside the loop, then use insertSheet() for each iteration and reference that specific sheet when setting values. I hit this same issue doing monthly data exports - you’ve got to keep your sheet references straight. Your loop logic works fine, just need to fix the file and sheet handling.

3

totally! U create a new file in every loop iteration. Just set SpreadsheetApp.create() outside the loop so you only create it once. And remember, instead of getting the full range of the file, use newSheet.getRange(1,1,totalRows,totalCols) for the specific sheet.

4

You’re creating a new file inside the loop, which leads to overwriting your previous work each time. Instead, create the target file once before starting the loop and add sheets to it. Move the file creation outside your loop as follows:

var newFile = SpreadsheetApp.create('Quarterly Archive');
var targetFile = newFile; // Directly reference the newly created file

for (var j=0; j<4; j++) {
    // existing sheet processing code remains the same
    var newSheet = targetFile.insertSheet(sheetTitle, 0);
    newSheet.getRange(1, 1, totalRows, totalCols).setValues(cellValues);
}

Additionally, be cautious with the getDataRange() method on targetFile—it retrieves the entire range of the file rather than just the specific sheet. It’s best to use the reference for the newly created sheet. I faced a similar challenge while building quarterly reports last year.