How I automated weekly document creation using Google Apps Script
We use Google Docs to create and collaborate on Weekly Business Reviews across various departments. An essential part of the process is document creation and distribution. So at the beginning of every week, a new document would need to be created (duplicated from last week’s review), renamed and emailed to all the stakeholders.
The responsibility for this task got assigned to a person. That person started following the process diligently and everything was fine. The problem started when the person went on a sick leave and suddenly, nobody received the document for the week’s discussion. Our team works across different timezones. A delay in sending the document would mean that some members would not be able to work on it at night.
I realized that the problem was not with people, but rather with the nature of the process.
Hence, I set out to write a Google Apps Script to automate the creation and distribution of the weekly review doc, which I describe below.
If you are not familiar with Google Apps Script, no worries, it is fairly easy to get started. Just open Google Sheets and go to Tools > Script Editor. In Google’s words,
Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.
I started by listing the steps that I would need to undertake:
- Decide upon a nomenclature for the review documents. My document name nomenclature is: “Weekly_Status_<Last Monday’s Date>__<Last Sunday’s Date>”
More specifically:
Weekly_Status_MMMDD_MMMDD_YYYY
For example, Weekly_Status_Sep25_Oct01_2017 - Search for last WBR (Weekly Business Review doc) from the relevant folder in GDrive based on nomenclature.
- Duplicate it and rename the new file according to nomenclature.
- Email the new file to relevant stakeholders with customized subject and body.
- Schedule the script to run on every Monday.
I start out by defining a date variable:
var varDate = new Date();My next step is to obtain the date for last-to-last Monday. I will use that to fetch the previous WBR and set dates for the new WBR.
I will format this date according to my nomenclature.
var prevDateMon= Utilities.formatDate(varDate, "GMT", "MMMdd_");Further,
Now that I have the name of last week’s WBR, I proceed to step 2, i.e., searching for the doc in GDrive. I start out by entering the relevant folder. Need to make sure I have permission to access it. Then we search for the doc in the folder:
We will make a copy of this doc and rename it to the new name according to our nomenclature (store it in a variable named newDate).
Next, I create a function called sendEmail(), to send the Email about the WBR to relevant users. It will take the URL of the new doc and new dates as parameters.
The last step is to set up the trigger to send this email weekly on Mondays. You can customize this to whatever time suits you. The triggers can be set by clicking on the clock icon beside the save icon in the script editor. Make sure you provide the permissions for the function to run.
That’s it! Your weekly reports will be automatically created and distributed as per the schedule chosen by you. ⏱✅
