Part 1: Automating Tedious Parts of Research Administration in Google Workspace

Research teams often find themselves bogged down by administrative tasks of the research process, like managing project trackers, developing an archive or creating research plans. This could be solved by automating some of the research process using Google Apps Script.

Here are the things that you need to consider before you begin programming automation into your process because it will help you decide (1) if you can automate your workflow, (2) whether automation is necessary, and (3) what you can automate in your workflow:

• Map out the research workflow: Identify what the documentation touchpoints are in your research process. These questions include:

  • Important: Is there a central source of truth for all research projects on your team?

  • How does your stakeholders reach out to you for research resourcing?

  • How does your team communicate with each other about what projects you are on?

  • What documents do you have to create and where is the project information coming from?

  • What files have already been templatized?

• Identify patterns: Find what data you are consistently copying from project trackers, research plans, etc.

• Discover informational needs or gaps: Discover what archival and documentation needs the team has but has struggled to keep updated. For example, keeping an archive of research reports, decreasing cost of repeated research.

• Data policies: Some companies have strict data policies, which might make implementing automation difficult. Please check in with your IT team about whether you can do this before jumping in.

  *ahem* This is coming from a person that was once flagged as an unauthorized developer, sometimes proactivity is not always a good thing.

What is Google Apps Script?

Google Apps Script utilizes JavaScript that allows you to integrate, and automate tasks across Google Drive products (e.g., Slides, Sheets, Docs, GMail, GCal.)

Based on my past experience, Google Apps Scripts has worked very well when it comes to retrieving data from a centralized source, preferrably data in a specified data container (i.e., a cell in Google Sheets.) It takes information from that specified data contrainer and duplicates it elsewhere, hence the need to highlight point 1 in “Map out the research workflow.”

Wiith Google Apps Scripts, you can:

• Automatically create a folder and generate documents to place within that folder

• Retrieve links from generated documents into your centralized project tracker (e.g., create an archive)

• Send automated email updates with specified information utilizing triggers within a document

Here is a YouTube video that would give you a snippet of what Google Apps Scripts can do. I have specified start times and end times of the video in the URL, which should take 1 minute and 10 seconds to view.

Resources to aid your work of automation

• Google Workspace Guides: You will find resources of the types of funtions that you would need, depending on the what products that you want to open (i.e., Google Docs, Google Sheets, GCal, etc.) and when you want it to trigger. It would also describe to you what the functions does and how to run the function.

Laying the Foundation for Automation

To start automating, you’ll need a files and templates. Think of these as the skeletons for the documents you use most. To start, please have the following files at hand:

• Research Project Tracker: This will function as your data hub where you would receive data from and write your data into. When you automate data writing

• Research Report Template: A Google Doc that auto-fills headers, dates, and project information.

• Email Drafts: Pre-written communications stored in Gmail drafts or Google Docs for easy customization and sending.

Now that you have those files on hand, I will go into Part 2 of how you can start automating your scripts. This is meant to help you get started but not meant to be perscriptive, because each team has their own needs.