Using Google Apps Script with user input to automate repetitive tasks in Google Docs

Do you find yourself ever doing repetitive tasks over and over again in Google Docs? (Or any of the Google Suite Apps?) I sure do. At my church, we create a Google Doc every week for all of the “talking points”, the parts of the service that aren’t song or sermon, where we script out what someone needs to say or communicate during that portion.

screen shot 2019-01-13 at 5.48.00 am
Here is a sample document that we use each week.

A couple years ago, I started creating template files to help my team do this every week, because having the template already there with some common headers, the service date, etc. removed the barrier to get down to writing the actual words. Creating the files wasn’t too complicated, and after awhile, I started making them “in bulk”, where I would sit down and just make 3-4 months worth of documents at a time, making copies of my master template, editing the new file and updating the date, etc. Then we added a second auditorium, which doubled the amount of documents I needed to create.

With the new year, it was time to create more documents, so I decided this time around that I would create a script to help automate this task using the framework within Google Apps Script.

If you’ve not heard of or used Google Apps Script (GAS), it’s a scripting language based on Javascript, for light-weight application development. All of the code runs on Google’s servers to interact with your documents. If you’ve ever used an “add-on” in Google Apps, it’s using this scripting framework.

It’s pretty easy to use if you know Javascript, and it’s easy to get started. From any document, just go to Tools > Script Editor. This opens a new tab where you can start writing Apps Script.

Here is my script:


function myFunction()
{
var ui = DocumentApp.getUi();

var templateDocId = '[templateid]'; // put the document ID of the master template file here

var prompt_numberOfDocs = ui.prompt('How many Talking Point Documents do you want to create?');
var prompt_startingDate = ui.prompt('What is the starting date? Please enter in MM/dd/yyyy.');

var numberOfDocs = parseInt(prompt_numberOfDocs.getResponseText());
var startingDate = prompt_startingDate.getResponseText();

var prompt_venueResponse = ui.prompt('Venue', 'Create Documents for both Auditoriums? If no, please type in the Venue Title and click "No".', ui.ButtonSet.YES_NO);

var venueTitle = '';

var bothAuditoriums = true;

if (prompt_venueResponse.getSelectedButton() == ui.Button.NO)
{
venueTitle = prompt_venueResponse.getResponseText();
bothAuditoriums = false;
}

var date = new Date(startingDate);

var htmlOutput = HtmlService
.createHtmlOutput('Creating ' + numberOfDocs + ' documents. Please stand by...

')
.setWidth(300)
.setHeight(100);

ui.showModalDialog(htmlOutput, 'Talking Points - Task Running');

for (var i = 0; i < numberOfDocs; i++)
{
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
var documentName = 'Talking Points - ' + Utilities.formatDate(loopDate, Session.getScriptTimeZone(), "MMMM dd, yyyy");
var documentDate = Utilities.formatDate(loopDate, Session.getScriptTimeZone(), "MM/dd/yyyy");
if (bothAuditoriums)
{
createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 1)', 'Aud 1', documentDate);
createNewTalkingPointDocument(templateDocId, documentName + ' (Aud 2)', 'Aud 2', documentDate);
}
else
{
documentName += ' (' + venueTitle + ')';
createNewTalkingPointDocument(templateDocId, documentName, venueTitle, documentDate);
}
}

htmlOutput = HtmlService
.createHtmlOutput('google.script.host.close();')
.setWidth(300)
.setHeight(100);
ui.showModalDialog(htmlOutput, 'Talking Points - Task Running');
}

function createNewTalkingPointDocument(templateDocumentId, documentName, venueTitle, documentDate)
{
//Make a copy of the template file
var documentId = DriveApp.getFileById(templateDocumentId).makeCopy().getId();

//Rename the copied file
DriveApp.getFileById(documentId).setName(documentName);

//Get the document body as a variable
var body = DocumentApp.openById(documentId).getBody();

//Insert the entries into the document
body.replaceText('##Venue##', venueTitle);
body.replaceText('##Date##', documentDate);
}

Once you have a script in place, you can choose triggers for when it should run, like when it is opened, or on a schedule, etc.

Here is the new template with the script in action:

screen shot 2019-01-13 at 6.10.10 am

First, I ask how many documents should be created. 1, 5, 500, whatever I need.

screen shot 2019-01-13 at 6.10.29 am

Next, I ask for the starting date. We specifically use these for Sunday services, so I’ve programmed the script to take this starting date and then calculate every 7 days when creating multiple documents.

screen shot 2019-01-13 at 6.10.44 am

Then, I ask the user if they want to create documents for both auditoriums, or if this is for a special service or off-site service, etc. Typically we want them for both auditoriums, but the one-off feature makes things easy for those types of services too.

screen shot 2019-01-13 at 6.10.57 am

As the script runs, it displays this dialog box. Creating that many documents can take awhile, and I wanted the user to be aware of this. The box goes away automatically when the process is completed.

Now that we have this, I can pass the task on to anyone on our team, anytime they need these documents! And it saves a good bit of time. I definitely spent less time creating this script than I would have spent creating the 3-4 months worth of documents manually, and now I never have to do that again!

How can you use Google Apps Script to automate some of your more repetitive tasks?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s