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 Sheet data
Screenshot of Google Sheet 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);


Download
Bulk create Google Docs from Google Sheet data download (please use 'File' > 'Make a copy' for your own version).

15 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
    2. Late to the party but I've edited this code to work with Google Sheets instead of Docs. It's farily simple and I can share if it's still needed! Also, thanks Phil! This saved our school hours of work.

      Delete
    3. Hi

      Great, glad to know you got sorted in the end and that it will save you time going forwards! I ended up developing a tool for this as it was required as part of a project I was working on, so it will appears on the blog in the next few weeks hopefully.

      Kind regards
      Phil

      Delete
  4. Phil, very impressive. Is it possible to do this with the capability to create the docs by automation as: "Anyone with link" can view - And then have that share link put in the column next to the person's name?

    ReplyDelete
  5. In response to a recent enquiry it is possible to put the tags (eg <>, <>) into a Table in a Google Docs and have that populate with information from the Google Sheet.

    Kind regards
    Phil

    ReplyDelete
  6. How would I go about creating other sheets instead of docs?

    ReplyDelete
    Replies
    1. Hi Tom

      It's perfectly possible, you would need to make a copy of a template Google Sheet (instead of a Doc in this example) - Line 192 in the above code.

      You would however then need to change the whole 'replaceText' process as this would not work in a Google Sheet. Instead you would have to target cells with 'getRange()' and then change their value via 'setValue()'.

      I'm afraid I don't yet have a specific example to refer too.

      Kind regards
      Phil

      Delete
  7. I've got a need for this, too.
    I'm organizing a school-wide program at a high school in California. Since we're in a Distance Learning format, I need to attach a rubric that's on a spreadsheet to a roster of students and share it with the entire staff. Your script will reproduce the spreadsheets, and I've adapted it to rename the files properly, but it won't modify the files or post the links. I don't know enough to make it work completely.

    It's been extremely helpful, though. We're holding our presentations next week, and I think it's actually going to go pretty smoothly, due in part to your script.
    so thanks!
    I owe you one.

    ReplyDelete
    Replies
    1. Hi

      Thanks for the feedback - I'm in the process of getting something together for this it's just taking some time outside of work. Glad you've got something sorted.

      Kind regards
      Phil

      Delete