Skip to content

Instantly share code, notes, and snippets.

@octipus

octipus/combinator.gs

Created January 26, 2023 16:24
Show Gist options
  • Save octipus/0e430f700bb0b78987661f85bb9b83f4 to your computer and use it in GitHub Desktop.
Save octipus/0e430f700bb0b78987661f85bb9b83f4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. octipus created this gist Jan 26, 2023.
    37 changes: 37 additions & 0 deletions combinator.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,37 @@
    //Setup:
    // 1. Files must be of type spreadhsheet - not text/csv
    // 2. All spreadsheet files must sit in the same G Drive folder
    // 3. Get the folder by ID and loop through the files
    // 4. Concatenate the results of all files in a separate spreadsheet (outside of the folder)

    function myFunction() {
    // get the folder ID
    var folder = DriveApp.getFolderById("insert-your-folder-id-here")
    var filesIterator = folder.getFiles()
    var file;
    var fileType;
    var combinedData = [];
    var data;

    while(filesIterator.hasNext()){
    file = filesIterator.next()
    fileType = file.getMimeType();
    if(fileType == "application/vnd.google-apps.spreadsheet") {
    ssID = file.getId();
    data = getDataFromSpreadsheet(ssID);
    combinedData = combinedData.concat(data)
    }
    }

    // getActiveSpreadsheet only works when we initialize this script from the spreadhseet file
    ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("combined");
    ws.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData)
    }

    function getDataFromSpreadsheet(ssID) {
    var ss = SpreadsheetApp.openById(ssID);
    var ws = ss.getSheets()[0];
    var data = ws.getRange("A2:T" + ws.getLastRow()).getValues();
    return data
    }