Screenshot of Google Sheet data |
There are some notable features implemented:
- Toast notifications have been used to inform the user of the progress of the script.
- Logging has been implemented to support troubleshooting any problems that may occur. It is a slightly improved version of this previous blog post.
- Try/catch supports the previous logging by capturing any errors within the script and outputting them to the Log sheet.
- Name:value pairs are used to pass the spreadsheet data to the Function that then creates the Google Docs.
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 Docs2) createDocs.gs
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();
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 FolderOnce 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:
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();
// open document file and get BodyThe final task is to create a link to the Google Doc in the spreadsheet next to the relevant row:
var newFileBody = DocumentApp.openById(newFileID).getBody();
// replace tags with data from sheet
newFileBody.replaceText('<<emailAddress>>', emailAddress);
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).
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?
ReplyDeleteHi Marcel
DeleteI 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
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..
ReplyDeleteHi Marcel
DeleteDid 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
Hi,
ReplyDeleteI'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.
Hi
DeleteI'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
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.
DeleteHi
DeleteGreat, 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
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?
ReplyDeleteLooked at this with Scott. Many thanks.
DeletePhil
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.
ReplyDeleteKind regards
Phil
How would I go about creating other sheets instead of docs?
ReplyDeleteHi Tom
DeleteIt'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
I've got a need for this, too.
ReplyDeleteI'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.
Hi
DeleteThanks 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
How can I create a new Folder and also a new doc inside that folder based on a sheet row. Thank you
ReplyDeleteHi
DeleteSo 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