{"id":1493,"date":"2022-11-07T11:48:48","date_gmt":"2022-11-07T16:48:48","guid":{"rendered":"https:\/\/techministry.blog\/?p=1493"},"modified":"2022-11-07T11:48:48","modified_gmt":"2022-11-07T16:48:48","slug":"google-apps-script-still-a-helpful-tool-to-automate-repetitive-tasks-in-google-docs-with-user-input","status":"publish","type":"post","link":"https:\/\/techministry.blog\/?p=1493","title":{"rendered":"Google Apps Script – still a helpful tool to automate repetitive tasks in Google Docs with user input"},"content":{"rendered":"\n

Awhile back<\/a>, I shared a post about using Google Apps Script (GAS) to automate repetitive tasks in Google Docs. 4 years later, we are still using this same script.<\/p>\n\n\n\n

My church recently announced that we would be launching an evening service, so I took the opportunity to open the script and add support for the additional document type. I also added conditional checks that if the user ever selects the ‘CLOSE’ button on one of the dialog boxes, the script will automatically end and no further action is taken.<\/p>\n\n\n\n

Here’s the updated script:<\/p>\n\n\n\n

function myFunction()\n{\n  var ui = DocumentApp.getUi();\n  \n  var templateDocId = '[templateid]';\n  \n  var prompt_numberOfDocs = ui.prompt('How many Talking Point documents do you want to create?');\n\n  if (prompt_numberOfDocs.getSelectedButton() == ui.Button.CLOSE) {\n    \/\/ don't go any further\n    return;\n  }\n\n  var prompt_startingDate = ui.prompt('What is the starting date? Please enter in MM\/dd\/yyyy.');\n\n    if (prompt_startingDate.getSelectedButton() == ui.Button.CLOSE) {\n    \/\/ don't go any further\n    return;\n  }\n  \n  var numberOfDocs = parseInt(prompt_numberOfDocs.getResponseText());\n  var startingDate = prompt_startingDate.getResponseText();\n  \n  var prompt_venueResponse = ui.prompt('Venue', 'Create Documents for a custom venue? Type in the venue name and click YES. Otherwise click NO.\".', ui.ButtonSet.YES_NO);\n  \n  var venueTitle = '';\n  \n  var customVenue = false;\n\n  var createAud1Morning = false;\n  var createAud2Morning = false;\n  var createAud1Evening = false;\n\n  if (prompt_venueResponse.getSelectedButton() == ui.Button.CLOSE) {\n    \/\/don't go any further\n    return;\n  }\n  else if (prompt_venueResponse.getSelectedButton() == ui.Button.YES)\n  {\n    venueTitle = prompt_venueResponse.getResponseText();\n    customVenue = true;\n  }\n  else {\n    var prompt_Aud1MorningResponse = ui.prompt('Venue', 'Create Documents for Aud 1 (Morning)?', ui.ButtonSet.YES_NO);\n    var prompt_Aud2MorningResponse = ui.prompt('Venue', 'Create Documents for Aud 2 (Morning)?', ui.ButtonSet.YES_NO);\n    var prompt_Aud1EveningResponse = ui.prompt('Venue', 'Create Documents for Aud 1 (Evening)?', ui.ButtonSet.YES_NO);\n\n    if (prompt_Aud1MorningResponse.getSelectedButton() == ui.Button.CLOSE) {\n      \/\/don't go any further\n      return;\n    }\n    else if (prompt_Aud1MorningResponse.getSelectedButton() == ui.Button.YES) {\n        createAud1Morning = true;\n    }\n\n   if (prompt_Aud2MorningResponse.getSelectedButton() == ui.Button.CLOSE) {\n      \/\/don't go any further\n      return;\n    }\n    else if (prompt_Aud2MorningResponse.getSelectedButton() == ui.Button.YES) {\n        createAud2Morning = true;\n    }\n\n    if (prompt_Aud1EveningResponse.getSelectedButton() == ui.Button.CLOSE) {\n      \/\/don't go any further\n      return;\n    }\n    else if (prompt_Aud1EveningResponse.getSelectedButton() == ui.Button.YES) {\n        createAud1Evening = true;\n    }\n  }\n  \n  var date = new Date(startingDate);\n  \n  var htmlOutput = HtmlService\n    .createHtmlOutput('<p>Creating ' + numberOfDocs + ' documents. Please stand by...<\/p>')\n    .setWidth(300)\n    .setHeight(100);\n  \n  ui.showModalDialog(htmlOutput, 'Talking Points - Task Running');\n  \n  for (var i = 0; i < numberOfDocs; i++)\n  {\n    var loopDate = new Date(date.getTime()+ ((i * 7) * 3600000 * 24)); \/\/ uses the looping interval to get the starting date and add 7 days to it, creating a new date object\n    var documentName = 'Talking Points - ' + Utilities.formatDate(loopDate, Session.getScriptTimeZone(), \"MMMM dd, yyyy\");\n    var documentDate = Utilities.formatDate(loopDate, Session.getScriptTimeZone(), \"MM\/dd\/yyyy\");\n\n    if (customVenue) {\n      documentName += ' (' + venueTitle + ')';\n      createNewTalkingPointDocument(templateDocId, documentName, venueTitle, documentDate);\n    }\n    else {\n      if (createAud1Morning) {\n        createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 1 Morning)', 'Aud 1 (Morning)', documentDate);\n      }\n\n      if (createAud2Morning) {\n        createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 2 Morning)', 'Aud 2 (Morning)', documentDate);\n      }\n\n      if (createAud1Evening) {\n        createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 1 Evening)', 'Aud 1 (Evening)', documentDate);\n      }\n    }\n  }\n  \n  htmlOutput = HtmlService\n    .createHtmlOutput('<script>google.script.host.close();<\/script>')\n    .setWidth(300)\n    .setHeight(100);\n  ui.showModalDialog(htmlOutput, 'Talking Points - Task Running');\n}\n\nfunction createNewTalkingPointDocument(templateDocumentId, documentName, venueTitle, documentDate)\n{\n  \/\/Make a copy of the template file\n  var documentId = DriveApp.getFileById(templateDocumentId).makeCopy().getId();\n      \n  \/\/Rename the copied file\n  DriveApp.getFileById(documentId).setName(documentName);  \n      \n  \/\/Get the document body as a variable\n  var body = DocumentApp.openById(documentId).getBody();\n    \n  \/\/Insert the entries into the document\n  body.replaceText('##VENUE##', venueTitle);\n  body.replaceText('##DATE##', documentDate);\n}<\/code><\/pre>\n\n\n\n
If you’re using Google Apps Scripts to automate something, leave a comment to share how! If you’d like to create something like this for your ministry and need my help, drop me a line<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Awhile back, I shared a post about using Google Apps Script (GAS) to automate repetitive tasks in Google Docs. 4 years later, we are still using this same script. My church recently announced that we would be launching an evening service, so I took the opportunity to open the script and add support for the … Continue reading “Google Apps Script – still a helpful tool to automate repetitive tasks in Google Docs with user input”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1493","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/techministry.blog\/index.php?rest_route=\/wp\/v2\/posts\/1493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techministry.blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techministry.blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techministry.blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techministry.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1493"}],"version-history":[{"count":0,"href":"https:\/\/techministry.blog\/index.php?rest_route=\/wp\/v2\/posts\/1493\/revisions"}],"wp:attachment":[{"href":"https:\/\/techministry.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techministry.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techministry.blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}