Google Sheet script to import range a folder to one sheet

Question

I was researching how to do what I needed and I found this video and thought it was PERFECT for me.

It shows how to set up a script on a page to import an entire folder, which is exactly what I need. I have 14 sheets that are identical. I need to copy A2:F from all of them into one sheet. Here is the script:

function getdata() {
    var files = DriveApp.getFolderById("0B94fkeHjleasRF9QMDFlcHllRVU").getFiles()
    while (files.hasNext()) {
      var file = files.next();
      var shoot = SpreadsheetApp.openById(file.getId());

      var sourcesheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
      var sourcerange = sourcesheet.getRange('A2:F');
      var sourcevalues = sourcerange.getValues();

      var destsheet = shoot.getSheetByName('Sheet1'); 
      var destrange = destsheet.getRange('A2:F'); 
      destrange.setValues(sourcevalues);         
   }
 }

The problem is: although it worked in the video, I can't seem to get it to work for me. I played with the last line (which where I am getting an error). I managed to get the script to delete all the information I needed to copy. Lol. So obviously I need help. Can anyone see any glaring mistakes in this script?

I have tried importRange but it hangs up on array formulas. This script seemed perfect, if only I could get it to work.

Currently, your code is getting the same values from the active sheet on every while loop. If you want the source data to be the data from the active sheet, and it's always the same range, you should put that outside of the loop. You'd only need to get the source data once. — Alan Wells
I have only done a few scripts. and I am afraid I dont understand what you are saying? I do see I messed up on the A2,F and changed that to 2,6. But that did not work either — Connie
There are 4 different variations of the getRange() method. Each takes either a different number of parameters, or a different type of parameter. I'm using a different variation in the code I'm proposing. — Alan Wells

Alan Wells Alan Wells · Accepted Answer · 2016-01-07T03:38:45

This code:

Gets files from a specific folder
Loops through all the files in the folder
Tests for the Mime Type of the file to get only spreadsheets
Gets all the values out of the source sheet tab
Writes all new data to the end of existing data

For this code to work, you must enter your folder ID, your source sheet tab name, and the destination spreadsheet file ID.

Code:

function getdata() {
  //declare multiple variables in one statement - Initial value will
  //be undefined
  var destinationSpreadsheet,destinationSS_ID,destsheet,destrange,file,files,
      sourcerange,sourceSS,sourcesheet,srcSheetName,sourcevalues;

  srcSheetName = "Type the Name of Source Sheet Tab Here";
  destinationSS_ID = "Type the Name of destination spreadsheet file ID Here";
  files = DriveApp.getFolderById("Folder ID").getFiles();
  destinationSpreadsheet = SpreadsheetApp.openById(destinationSS_ID);
  destsheet = destinationSpreadsheet.getSheetByName('Sheet1');  

  while (files.hasNext()) {
    file = files.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") {
      continue;
    };
    sourceSS = SpreadsheetApp.openById(file.getId());
    sourcesheet = sourceSS.getSheetByName(srcSheetName);
    //sourcesheet.getRange(start row, start column, numRows, number of Columns)
    sourcerange = sourcesheet.getRange(2,1,sourcesheet.getLastRow()-1,6);
    sourcevalues = sourcerange.getValues();

    //Write all the new data to the end of this data
    destrange = destinationSpreadsheet.getSheetByName("Sheet1")
        .getRange(destsheet.getLastRow()+1,1,sourcevalues.length,sourcevalues[0].length);

    destrange.setValues(sourcevalues);         
  };
};

Google Sheet script to import range a folder to one sheet

1 Answers