Pages

Tuesday, 26 May 2020

Bulk create Google Docs from Google Sheet data

The following Google Apps Script is designed to bulk create Google Docs from rows of data within a Google Sheet and to include some of that data within the new Doc body (so they become customised files). It also creates a link to the new Google Doc back in the Sheet on the relevant row.
Screenshot of Google Doc data
Screenshot of Google Doc data

There are some notable features implemented:
  1. Toast notifications have been used to inform the user of the progress of the script.
  2. Logging has been implemented to support troubleshooting any problems that may occur. It is a slightly improved version of this previous blog post.
  3. Try/catch supports the previous logging by capturing any errors within the script and outputting them to the Log sheet.
  4. Name:value pairs are used to pass the spreadsheet data to the Function that then creates the Google Docs.
1) getSpreadsheetData.gs
This Function gets data from the Google Sheet - including IDs for the Google Drive folder to store the created Docs, the template file which will be copied for each row and the data itself:
// get Drive Folder ID for storing created Docs
var destinationFolderId = configSheet.getRange(1, 2).getValue();


// get Template File ID
var templateFileId = configSheet.getRange(2, 2).getValue();


// get all data as a 2-D array
var data = dataSheet.getDataRange().getValues();
2) createDocs.gs
This Function takes the data from the Google Sheet and loops through each row to create a Google Doc. It makes use of 'try/catch' in order to capture any errors that could occur with the process to output them into the spreadsheet. The template document (which is copied each time) and the destination folder (location to save the new Google Docs) have their IDs stored in the Config sheet so that they can be easily updated:
// get destination Folder
var destinationFolder = DriveApp.getFolderById(destinationId);


// get Template File
var templateFile = DriveApp.getFileById(templateFileId);


// make copy of Template File and place in destination Drive Folder
var newFile = templateFile.makeCopy(newFileName, destinationFolder);


// get ID and Url of new File
var newFileID = newFile.getId();
var newFileUrl = newFile.getUrl();
Once we have the new Google Doc its ID we can open it, get the body of the document and replace our tags with data from the Google Sheet:
// open document file and get Body
var newFileBody = DocumentApp.openById(newFileID).getBody();


// replace tags with data from sheet
newFileBody.replaceText('<<emailAddress>>', emailAddress);
The final task is to create a link to the Google Doc in the spreadsheet next to the relevant row:
var newFileLink = '=HYPERLINK("' + newFileUrl + '","' + newFileName + '")';
dataSheet.getRange(i+1, 5).setFormula(newFileLink);

Bulk create Google Docs from Google Sheet data

6 comments:

  1. Hi! I believe that I may have some use for your script by its description. I know absolute nothing about google scripts and have never used before. So, not to take too much of your time, do you have any hint or tutorial where I can start reading in order to use this code?

    ReplyDelete
    Replies
    1. Hi Marcel

      I kind of started here: https://developers.google.com/apps-script/articles/tutorials
      But I also had some colleagues at work who guided me.

      Kind regards
      Phil

      Delete
  2. The reason for my earlier question was that I tried to run this script in "script editor" in a new google sheet, but I got the error "SyntaxError: Unexpected token '}' (linha 82, arquivo "Código.gs")".. from there I got lost..

    ReplyDelete
    Replies
    1. Hi Marcel

      Did you download and use my copy (https://bit.ly/2VABAQt)? There's a 'logEvent' just before that line which requires a specific Google Sheet to exist.

      Kind regards
      Phil

      Delete
  3. Hi,

    I'm getting the same error:
    SyntaxError: Unexpected token '}' (line 82, file "1) getSpreadsheetData.gs")

    However I'm seeing a closed bracket on line 83 that does not have a matching open bracket. Your brackets, as far as I can tell, go:

    function onOpen: line 2 thru 9
    function logEvent: line 12 thru 32
    function getSpreadsheetData: line 35 thru 76

    Line 80: / if failed set variable as false and Log
    Which is followed by (81)"var openNewFile"; (82)logEvent & then (83)a closed bracket.

    I've only been able to delve into GAS for about a month so I may be totally still clueless here!

    btw, thank you for this code! I've been searching for code which will allow me to automate a report my company does on Google Docs using info from Google Sheets.

    ReplyDelete
    Replies
    1. Hi

      I've just taken a copy of the file and run it as a general individual and it all worked perfectly fine for me.

      There's no 'line 82' in the 'getSpreadsheetData.gs' file so I'm not sure how that error message has been generated.

      All of the code/brackets are fine as I say, as I've just ran through the whole thing ...

      Kind regards
      Phil

      Delete