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 (1 minute and 10 seconds) that would give you a snippet of what Google Apps Scripts can do.

Resources to aid your work of automation

Google Workspace Guides: You will find resources of the types of functions 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.
Other examples: Templatized recruitment emails, communication emails, etc.

How to access Google Apps Scripts

As mentioned in the previous post, my suggestion is to start with a centralized database (i.e., Google Sheets). This is where Google Apps Scripts will pull the data from and will insert it into the appropriate file types (i.e. Docs, Slides, etc.)

The image below shows a step-by-step process of how to get to Google Apps Script.

Go to Navigation —> Extensions —> Apps Scripts

Tips before you begin

Each file type has specific functions. For example Google Drive functions will be DriveApp.action, while Google Doc functions will be DocumentApp.action. Refer to the Google documentation/API.
- NOTE: Planning what you need/want to do will decrease how much you would have to refer to that site, think back to information architecture (IA).
For every new function that you create, test it out with Logger.log(data or function). This will show up in your “Execution Log” to see if your code is actually working.
- Here is an example of what you can write to test a function using Logger.log
```
// Log the number of Google Groups you belong to.
const groups = GroupsApp.getGroups();
Logger.log('You are a member of %s Google Groups.', groups.length);
```

The highlight in red is an example of where Logger.log would appear.

In your template, you have to put unique placeholder labels so that Google Apps Script knows where to the information. Below is a visual example.

Developing Google Apps Script code

Creating a menu

Personally, I like some user control with auto-generated files because it acts as a signpost that aids with cognitive transitions. I created clickable dropdown menu buttons that initiate specific scripts to run. In the end, it depends on the teams’ workflow.

Here is what I would do to create a navigational menu that would trigger certain scripts.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
   ui.createMenu('Generating Documents')
      .addItem('Research plan', 'scriptoneGenerateRP')
      .addItem('Research report', 'scripttwoGenerateReport')
      .addToUi();
}

The image below is how the menu would look like when launched.

Automating sub-folder creation

Now, I want to auto-create subfolders from Spreadsheet Data where the spreadsheets will live. I recommend that you and your team members do this to facilitate organization in your centralized archive. I have a code here that says const main_file = DriveApp.getFileById("MainfileID_name"), this is the parent folder that your team should have in Google Drive. The way that you find the ID is shown in the image below.

Image taken from Stack Overflow, where to take fileID

Decide how you want the function to start, for example triggering when a piece of information changes or when a button is clicked. I added visual notes to explain the object logic. Underlined parts in the script should be filled with your data. After it runs, get the Google Folder URL so you can create new docs inside the new subfolder.

function createFolders() {

       const ss = SpreadsheetApp.getActiveSpreadsheet();

       const sheet = ss.getSheetByName(‘tab_name‘);

  const main_file = DriveApp.getFileById("MainfileID_name");

//create dialog box and generate info based on what is provided

  const ui = SpreadsheetApp.getUi();

       const response = ui.prompt(‘Title of dialog box', ‘row number:’, ui.ButtonSet.YES_NO);

//If dialog box text submission occurs, then generate folder.

       if(response.getSelectedButton() == ui.Button.YES) {

  let row = Number(response.getResponsetext());

  let projectNumber = sheet.getRange(row, columnNum).getValue();

  let projectName = sheet.getRange(row, columnNum).getValue();

  let folderName = projectNumber + projectName;

// Retrieve and save newly generated link in Google Sheets

       let folderLink =. main_file.createFolder(folderName);

‍       sheet.getRange(row, columnNum).setValue(folderLink.getURL());}

Automating research template generation

Here are a few things you would need for auto-generating documents:

Find submenu function: Refer back to "Creating a menu” section. For our specific purposes, our submenu functions are scriptoneGenerateRP() and scripttwoGenerateReport(). But we will be focusing on scriptoneGenerateRP.
Template document ID: Refer back to “Automating sub-folder creation” to learn how to get documentID

function scriptoneGenerateRP() {

//similar to createFolder function

       const ss = SpreadsheetApp.getActiveSpreadsheet();

       const sheet = ss.getSheetByName(‘tab_name‘);

  const main_file = DriveApp.getFileById("MainfileID_name");

  const ui = SpreadsheetApp.getUi();

       const response = ui.prompt(‘Title of dialog box', ‘row number:’, ui.ButtonSet.YES_NO);

       if(response.getSelectedButton() == ui.Button.YES) return;

// getting data from prompt and getting subfolder that had been previously generated

       const row = Number(response.getResponseText());

       const folderURL = sheet.getRange(row, columnNum).getValue().toString.trim();

       const targetFolder = DriveApp.getFolderById(folderURL[0]);

       try{

//Fetching data, repeat as needed

       let var1 = sheet.getRange(row, columnNum).getValue;

       let var2 = sheet.getRange(row, columnNum).getValue;

//Getting template, copying it and inputting the data from above

       const templateID = ‘documentID’;

       const copy = DriveApp.getFileById(templateID).makeCopy("Title", targetFolder);

       const doc = DocumentApp.openById(copy.getID);

       const body  = doc.getBody();

//Replacing placeholders with information from spreadsheet

       body.replaceText(‘{{Placeholder1}}’, var1);

       body.replaceText(‘{{Placeholder2}}’, var2);

And there you have it! You just have to click “Run” and “Deploy” the flow.

I know that AI vibecoding is all the range now, but personally it sometimes create inefficient code or hallucinates code that feels logical. A preliminary foundation of Javascript and AI vibecoding would be important for that process.

Automating Tedious Parts of Research Administration in Google Workspace