CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Ng Ct
Ng Ct

Reputation: 51

How to append all Google spreadsheets from a google folder?

I have a master Google Sheet and a google folder containing 5-6 sheets.

How to append all those 5-6 sheets into the master sheet with GAS? (They are all with same headers.), so that I can automate this action by changing the Folder ID.

I found the below which could identify all sheets in the folder but no know the next steps. Please help.

function listFilesInFolder(folderName) {

var sheet = SpreadsheetApp.getActiveSheet();

//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
var folder = DriveApp.getFolderById("***ID***");
var contents = folder.getFiles();

var cnt = 0;
var file;

while (contents.hasNext()) {
    var file = contents.next();
    cnt++;

Upvotes: 0

Views: 476

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Answer:

You can get the data using SpreadsheetApp and append the values using getRange().setValues().

Code example:

This is made a little generically as I do not know the structure of your folder/sheets:

function appendData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var folder = DriveApp.getFolderById("folderId");
  var files = folder.getFiles();
  
  while (files.hasNext()) {
    var tempFile = "";
    var file = files.next();
    // if the file is not a Sheet, skip it
    if (file.getMimeType() != "application/vnd.google-apps.spreadsheet") {
      tempFile = { 
        "title": file.getName(), 
        "parents": [ {
          "id": folderId
        }] 
      }; 

      file = Drive.Files.insert(tempFile, file.getBlob(), { "convert": true });
    }
      
    var importSheet = SpreadsheetApp.openById(file.getId())
    // assuming you only want to append the first sheet of each Spreadsheet:
    var range = importSheet.getSheets()[0].getDataRange();
    
    // extend the main sheet if you need to:
    try {
      sheet.getRange(1, 1, sheet.getDataRange().getNumRows() + range.getNumRows(), 1);
    }
    catch (e) {
      sheet.insertRowsAfter(sheet.getDataRange().getNumRows(), range.getNumRows() + 1);
    }    
    // remove the headers from the data to copy:    
    range = importSheet.getSheets()[0].getRange(2, 1, range.getNumRows() - 1, range.getNumColumns())
    
    // copy the data:
    sheet.getRange(sheet.getDataRange().getNumRows() + 1, 1, range.getNumRows(), range.getNumColumns()).setValues(range.getValues());
    if (tempFile != "") {
      Drive.Files.Delete(file.getId());
  }
}

Things this code does:

  • Gets the files from the specified folder
  • Converts the file to a Spreadsheet file if it's an Excel file
  • Uses SpreadsheetApp to get the Sheet data
  • Checks to see if the sheet to import to has enough rows, and adds the needed rows if it doesn't
  • Removes the headers from the data to import so it doesn't get duplicated
  • Copies in the data
  • Deletes the temp file if the file was an Excel file

Make sure to enable the Advanced Drive Service from the Resources > Advanced Google services... menu item.

References:

Upvotes: 1

Related Questions