Merging Google Docs from links in a Google Spreadsheet, selecting only some of them

Question

So, I have a Google Spreasheet in which I created a Google Doc for each entry (using a template) and added, at the end of each row, the link to the corresponding document. So far, so good. The problem is that now I want to able to combine several of the documents into one and I don't seem to manage, because I want to be able to export different combinations of them. In all examples I found (like this one) you need to enter the DocumentIDs or merge all the documents in a folder or spreadsheet.. and I want something in which I can write "OK" in the first column of the entries I want to combine (My status column) and then use a script to say

"if the status column says 'OK', then retrieve the ID from DocID column and combine it with all the rest of the DocIDs that also say 'OK', regardless of how many they are".

So far the only way I manage to do it is by manually entering the DocIDs:

function mergeGoogleDocs() {

  var docIDs = ['docID_1','docID_2','docID_3','docID_4'];
  var baseDoc = DocumentApp.openById(docIDs[0]);

  var body = baseDoc.getActiveSection();

  for (var i = 1; i < docIDs.length; ++i ) {
    var otherBody = DocumentApp.openById(docIDs[i]).getActiveSection();
    var totalElements = otherBody.getNumChildren();
    for( var j = 0; j < totalElements; ++j ) {
      var element = otherBody.getChild(j).copy();
      var type = element.getType();
      if( type == DocumentApp.ElementType.PARAGRAPH )
        body.appendParagraph(element);
      else if( type == DocumentApp.ElementType.TABLE )
        body.appendTable(element);
      else if( type == DocumentApp.ElementType.LIST_ITEM )
        body.appendListItem(element);
    }
  }
}

But this is tedious if I want to merge more than 4 or 5 files and I have to manually go get the information in the table instead of filtering my data, writing OK in the status column and let Google Scripts combine all files for me.

Does anybody have an idea how to do this? Thanks!

Answer 1

Solution:

Populate your docID array first from the spreadsheet with this code using Sheets API before merging. This assumes you have the OK status column in col A and the IDs in col B.

Sample Code:

function mergeGoogleDocs() {
  // Populate document IDs
  var docIDs = [];
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getRange(1,1,ss.getLastRow(),2).getValues();

  for (j = 0; j < range.length; j++) {
    if (range[j][0] == 'OK') {
      docIDs.push(range[j][1]);
    }
  }

  // Merge documents
  var baseDoc = DocumentApp.openById(docIDs[0]);

  var body = baseDoc.getActiveSection();

  for (var i = 1; i < docIDs.length; ++i ) {
    var otherBody = DocumentApp.openById(docIDs[i]).getActiveSection();
    var totalElements = otherBody.getNumChildren();
    for( var j = 0; j < totalElements; ++j ) {
      var element = otherBody.getChild(j).copy();
      var type = element.getType();
      if( type == DocumentApp.ElementType.PARAGRAPH )
        body.appendParagraph(element);
      else if( type == DocumentApp.ElementType.TABLE )
        body.appendTable(element);
      else if( type == DocumentApp.ElementType.LIST_ITEM )
        body.appendListItem(element);
    }
  }
}

Sample Data:

References:

Class Sheet