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).

34 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
  8. How can I create a new Folder and also a new doc inside that folder based on a sheet row. Thank you

    ReplyDelete
    Replies
    1. Hi

      So you would need to work from the beginning of the loop (line 184 onwards) and use the DriveApp to create a new folder (https://developers.google.com/apps-script/reference/drive/folder#createfoldername), then you would be able to create your document inside of it (like we do on line 196).

      As it's in the loop that step would be repeated for each row: hence a new folder with a document in it per row.

      Kind regards
      Phil

      Delete
  9. This is amazing - I realize I'm asking too much of you, but wonder if it's possible to email the resulting docs to each person on the list? Basically if I had 200 students with different data and wanted each of them to get an email containing their specific document, is this a whole different project or could it be included into this?

    ReplyDelete
    Replies
    1. Thank you.

      Yes that is perfectly feasible, I have this blog post which shows you how to send emails with data from a Google Sheet (https://www.pbainbridge.co.uk/2019/09/bulk-send-emails-from-google-sheet.html).

      It would be a matter of copying over the functionality into this project. I typically separate the two so that the documents can be created first, then emails sent at a later date.

      I do freelance work if you need support with this (just use the Contact Form on the blog) - otherwise you're more than welcome to make copies and adapt as you need. Remember you will need to share the Google Docs too.

      Kind regards
      Phil

      Delete
  10. Hi, how can I use the script and add customized images to each google doc as well? Or does this only do text? It'd be awesome if customized images can be added to each doc

    ReplyDelete
    Replies
    1. Hi Ukz

      This tool only handles text currently, I have not yet worked with images in Google Docs.

      Kind regards
      Phil

      Delete
  11. Hello Philly. This is a great tool! I have one question, how do I bulk set the permissions of each of the documents using the email column and send them emails accordingly? Thanks!

    ReplyDelete
    Replies
    1. Hi Mona

      Thank you! I have this blog post on creating Drive folders and adding permissions (via email address) - see '4) addPermissions.gs'

      You could use the DriveApp once you have created each Google Doc and then add the permissions level you require. This would by default send an automated email to the person about a new item being shared with them. You could look to send a customised email however (though this is a lot more work) example: https://www.pbainbridge.co.uk/2019/09/bulk-send-emails-from-google-sheet.html

      If you require a robust system/solution then feel free to use the Contact Form at the top of this page to discuss your requirements.

      Kind regards
      Phil

      Delete
    2. Thank you Phil. I managed to bulk share the documents via a GAM script, is there a way to extract created documents ids?

      Delete
    3. Hi Mona

      Yes when you create the Google Doc (via Apps Script) you can get lots of other details such as its ID. Line 199 in the embedded code above captures this for each file already.

      Kind regards
      Phil

      Delete
  12. Hi Phil,

    thanks for great work, it helped me a lot!
    I just want to ask, when all links are generated I get message: ReferenceError: e is not defined. Could you please help?
    Thanks.

    Kind Regards,
    Jan

    ReplyDelete
    Replies
    1. Hi Jan

      You're welcome!

      Hhhmmm I've not come across that one before. So it creates all of the Docs/Links and puts them into the Sheet correctly? You are only logged into one Google Account at the time you are running the process?

      Does it always happen? No matter if you're creating 1 Doc or 10 Docs?

      Kind regards
      Phil

      Delete
    2. Hi Phil,

      yes, I tried it with one row in sheet and same error. It´s funny because I get link to new file and everything seems ok. But it shouldn´t show error message obviously....I tried to send the link to another people and they got the same error. And I am logged to one Google account only. You can see it here: https://prnt.sc/bL8x9y17qxt7 Any idea?
      Thanks,

      Jan

      Delete
    3. Solved, ChatGPT helped :) Variable e wasn´t declared. Here is the code I pasted and it´s working. Maybe somebody will be looking for solution soit could help:

      var e = "something";
      console.log(e);

      Regards,
      Jan

      Delete
    4. Hi Jan

      I've tested the tool again a few times and have no issue at all. The only 'e' present is for the try/catch if the script encounters a failure when running. By declaring the variable in that way you are potentially overriding the error message ...

      Kind regards
      Phil

      Delete
  13. Hey Phil, I'm having trouble getting the tags to work :\ I even tried doing it so that I didn't change the original column names and the new docs don't show the information (though oddly enough, they show the second column's data in the doc title). Any tips?

    ReplyDelete
    Replies
    1. Hi Camille
      Are you comfortable with which bits of the code you will need to change in order to match up your Sheet data with the tags in the Document?

      Lines 186 to 190 in the embedded code above is where you get the data from the Sheet and store them in variables. Lines 88 to 92 are where you call these variables and use them to replace the tags in your Document.

      Everything must match up in terms of spelling and capital letters.

      Ta

      Delete
    2. That works! Admittedly, jumped the gun a bit with how exciting of a discovery this was! Thank you so much for this -- it will be a massive time saver for our team :)

      Delete