Google App Script to merge all documents from a GDrive Folder into a single GDocs document using an intermediate GSpreadsheet

README.md

This is a proof of concept to merge over 40 documents in a google drive folder into a single google docs document. It was inspired by this blog post (but completely rewritten).

Steps to use:

1. Create or open a spreadsheet in google docs.
2. add the content of Code.gs to the spreadsheet of via 'Tools' -> 'Scripteditor...'
3. save the script and reload the spreadsheet
4. put the id or url of a google drive folder (you can view) into any cell in the spreadsheet and select that cell
5. From the menu 'Merge folder into document' select 'List files and folders', this adds a new sheet containing the structure of the selected folder.
6. after tweaking the result of this step, e.g. by changing the titles or order of documents,
7. select the rows that should be included into the target document

• at the moment only continuous selections are supported
• folders don't have an affect on the resulting document yet.

8. go into the line 143/144 in the script and add the id of the document that should get filled with the merged document or choose to let it create a new one (each time the next step is executed).
9. From the menu 'Merge folder into document' select 'Merge selected rows'
10. look at the merged document :)

Any ideas on how to develop this script locally instead of in the google script editor (with the API availabel for tye hinting) or contributions are welcome.

Code.gs

var C_TYPE = 'Type',
    C_TITLE = 'Title',
    C_ID = 'ID',
    C_URL = 'URL',
    C_MIME_TYPE = 'file type',
    C_ELEMENTS = 'elements in document';
var HEADERS = [C_TYPE, C_TITLE, C_ID, C_URL, C_MIME_TYPE, C_ELEMENTS];


function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "List files and folders", functionName: "listFolderDetect"});
  menuEntries.push({name: "Merge selected rows into document", functionName: "mergeSelectedRows"});
  //   menuEntries.push(null); // line separator
  //TODO: add usage documentation
  ss.addMenu("Merge folder into document", menuEntries);
}

function getFolderIdFromInput(inputText, failAlertText){
  //support for input of raw id
  var id = inputText;
  
  /*supported urls:
    https://drive.google.com/drive/folders/0B0QnwP7NWSBRQmRNeVFUWWMzblk
    https://drive.google.com/open?id=0B0QnwP7NWSBRQmRNeVFUWWMzblk
    https://docs.google.com/folderview?id=0B0QnwP7NWSBRd21SdHNseElQclU&usp=drivesdk
  */
  if(inputText.indexOf('.google.com/')>0){ 
    if(inputText.indexOf('/folders/')>0){
      var noParams = inputText.split('?')[0];
      id = noParams.split('/').pop();
    }else if(inputText.indexOf('?id=')>0){
      var correctStart = inputText.split('id=').pop();
      id = correctStart.split('&')[0];
    }
  }
  if(id.length > 0){
    try{
      var f = folder(id);
      if(f){
        return(f.getId())
      }
    }catch(e){
      Logger.log(e)
      if(failAlertText){
        ui.alert(failAlertText + e);
      }
    }
  }
}

function listFolderDetect() {
  var ui = SpreadsheetApp.getUi();
  
  var selection = SpreadsheetApp.getActiveRange().getDisplayValue();
  var folderId = getFolderIdFromInput(selection, 'The selected value "'+selection+'" is not a folder.');
  if(!folderId){
    
    var prompt = ui.prompt('Select Folder', 
                           'You can either copy the id or the url of the folder', 
                           ui.ButtonSet.OK_CANCEL
                          );
    if(prompt.getSelectedButton() == ui.Button.OK){
      var userValue = prompt.getResponseText();
      folderId = getFolderIdFromInput(selection, 'The entered value "'+userValue+'" is not a folder.');
    }
  }
  if(folderId){
    listFolder(folderId);
  }
}

function listFolder(folderId) {
  //TestMerge
//  var testFolder = folder('0Byyr9iACA63XQTJTblIzRkNXbVE');
  //Integrated Plan
//  var intPlanFolder = folder('0B0QnwP7NWSBRQmRNeVFUWWMzblk');
  var sourceFolder = folder(folderId);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var now = Utilities.formatDate(new Date(), "UTC", "yyyy-MM-dd HH:mm:ss");
  var target = ss.insertSheet(sourceFolder.getName()+'('+sourceFolder.getId()+')'+now);
//  var target = SpreadsheetApp.getActiveSheet();
  target.appendRow(HEADERS);
  function modifyTitle(name){
    var filter = /(?:IntPl-\w\d?-)?(.*)/;
    return filter.exec(name).pop();
  }
  function addFolder(folder){
    target.appendRow(
      [
        'Folder',
        modifyTitle(folder.getName()),
        folder.getId(),
        folder.getUrl(),
      ]
    )
  }
  function addFile(file){
    var elements = ' --- '
    if(file.getMimeType() == 'application/vnd.google-apps.document'){
      var doc = DocumentApp.openById(file.getId());
      var elements = doc.getBody().getNumChildren();
    }
    target.appendRow(
      [
        'File',
        modifyTitle(file.getName()),
        file.getId(),
        file.getUrl(),
        file.getMimeType(),
        elements
      ]
    )
  }
  
  scanFolder(sourceFolder, addFile, addFolder);
  
}
    
function test(){
//  var intPlanFolder = folder('0B0QnwP7NWSBRQmRNeVFUWWMzblk');
//  function log(it){
//    Logger.log(it);
//  }
  
//  scanFolder(intPlanFolder, log, log)
  
//  Logger.log(getFolderIdFromInput('https://drive.google.com/drive/folders/0B0QnwP7NWSBRQmRNeVFUWWMzblk'));
}

function mergeSelectedRows(){
  var sheet = SpreadsheetApp.getActiveSheet()
  var selection = sheet.getActiveRange();
  var startRow = selection.getRow();
  var i = startRow == 1 ? ++startRow : startRow;
  var endRow = selection.getLastRow();
  
  //  TODO: let user input the document id or url, instead of creating a new one
  //  
  
  var docName = sheet.getName().split('(')[0];
  var showUrlOnComplete = false;
//  var targetDoc = DocumentApp.create(docName); showUrlOnComplete = true;
//  var targetDoc = DocumentApp.openById('1BbbsH1pVnbtzn2vJfj2l94hpwKb91uFZze2LeW4HBKA');
  var target = targetDoc.getBody().clear();
  
  var sections = [];
  //TODO table of content
  Logger.log({from:startRow, to:endRow});
  for(;i <= endRow;i++){
    var type = getValue(i, C_TYPE);
    if(type == 'Folder'){
      sections.push({title:getValue(i, C_TITLE),row:i});
    }else{
      var mime = getValue(i, C_MIME_TYPE)
      if(mime == 'application/vnd.google-apps.document'){
        var doc = DocumentApp.openById(getValue(i, C_ID));
        var source = doc.getBody()
        var elements = source.getNumChildren();
        var wasPageBreak = false
        for(var e = 0; e<elements; e++){
          var elem = source.getChild(e);
          wasPageBreak = false;
          switch(elem.getType()){
            case DocumentApp.ElementType.PARAGRAPH:
              target.appendParagraph(elem.asParagraph().copy());
              break;
            case DocumentApp.ElementType.PAGE_BREAK:
              wasPageBreak = true;
              target.appendPageBreak(elem.asPageBreak().copy());
              break;
            case DocumentApp.ElementType.LIST_ITEM:
              target.appendListItem(elem.asListItem().copy());
              break;
            case DocumentApp.ElementType.TABLE:
              target.appendTable(elem.asTable().copy());
              break;
            default:
              Logger.log('unhandeled element type %s',elem.getType());
          }
        }
        if(!wasPageBreak){
          target.appendPageBreak();
        }
      }
    }
  }
  if(showUrlOnComplete){
    SpreadsheetApp.getUi().prompt(targetDoc.getUrl());
  }
}

function getValue(row, columnLabel){
  var col = HEADERS.indexOf(columnLabel)
  if(col == -1) return col;
  col++;
  var sheet = SpreadsheetApp.getActiveSheet()
  return sheet.getRange(row, col).getDisplayValue();
}



function scanFolder(folder, addFile, addFolder){
  var files = folder.getFiles();
  var fileMap = {};
  var list = [];
  while (files.hasNext()){
    var file = files.next()
    var name = file.getName();
    fileMap[name] = file;
    list.push(name);
  }
  var subFolders = folder.getFolders();
  var folderMap = {};
  while (subFolders.hasNext()){
    var subFolder = subFolders.next();
    var name = subFolder.getName();
    folderMap[name] = subFolder;
    list.push(name);
  }
  list = list.sort();
  
  for (var i=0; i<list.length; i++){
    var name = list[i];
    if(fileMap.hasOwnProperty(name)){
      addFile(fileMap[name]);
      //in case of same name for file and folder the name will be key in both and it will be in the list twice, so we delete it from filesMap
      delete fileMap[name];
    }else if(folderMap.hasOwnProperty(name)){
      subFolder = folderMap[name];
      addFolder(subFolder);
      scanFolder(subFolder, addFile, addFolder);
    }
  }
  
}

function folder(id) {
  return DriveApp.getFolderById(id);
}
function file(id) {
  return DriveApp.getFileById(id);
}