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

As mentioned in the previous post, my suggestion is to start with Google Sheets to have a centralized database. 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.)

In this post, I am not going to use developer language because my aim is not make it understandable not technically accurate. I will also do my best to couple the work with imagery to make it easy to understand.

How to access Google Apps Scripts

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. Again, refer to the previous post where I link 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 placeholder labels so that Google Apps Script knows where to place the Google Sheets information when the code runs. An example shown below for Google Slides

Start of code

For me personally, I tend to like some user control with auto-generation of files because that helps me cognitive process where I am in the research. So I am going to have clickable buttons to initiate the running of specific scripts.

Here is what I would do to create a navigational menu for certain scripts to run.

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

This is how it would look like once launched and when function is clicked, it will run the designated scripts in your Google Apps Scripts project.

Create sub-folders

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 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

Here would be the script of how to create folders

function createFolders() {

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

  const sheet = file.getActiveSpreadsheet();

  const rowNumber = parseInt(prompt("Enter the row number to use:"))-1;

  const projectNumber = sheet.getRange(rowNumber, 1).getValue();

  const projectName = sheet.getRange(rowNumber, 2).getValue();

  // Create the folder name.

  const folderName = projectNumber + " - " + projectName;

  // Create the folder.

  DriveApp.createFolder(folderName);

}