Streamlining Business Processes With Google Apps Script

In a fast-paced environment, automating repetitive tasks and streamlining workflows can significantly improve productivity and efficiency. As Technical Program Managers and Program Managers (T/PgMs), we have the responsibility to streamline organizational workflows to efficiently and predictively produce outputs.  

In today’s workplace, T/PgMs have various tools and systems they can leverage to augment existing or newly created workflows. This creates a challenge where data can be fragmented across different databases and systems. Historically, people had to manually piece together various data elements to supplement their operations. However, in this article, we will discuss Google Apps Script and how it can integrate with other Google Workspace products to automate business-critical processes and increase the productivity of individuals. To highlight the importance of the technology, I will be focusing on a typical workflow of an organization and illustrate how Apps Script can be used to automate it.

Google Apps Script is a JavaScript-based scripting language that allows you to extend and automate various Google Workspace applications, such as  Google Docs and Google Sheets. With its rich set of APIs and services, Apps Script enables you to create custom functions, add-ons, and automation workflows within the Google Workspace Ecosystem.

Benefits of Google Apps Script for Google Docs Automation

1. Time-Saving Automation

Using Apps Script, you can automate repetitive tasks in any Google App, saving time and effort. For example, you can automate the creation of documents, generate reports, extract data from documents, and perform bulk edits or formatting changes.

2. Seamless Integration

Apps Script seamlessly integrates with other Google services such as Google Drive, Gmail, Sheets, and Calendar. This allows you to leverage the power of these services to build comprehensive automation workflows that span across multiple Google Workspace applications. It also allows for integration with external systems through API calls in order to retrieve real-time data directly into Google Docs. This integration allows for the seamless synchronization of information, eliminating manual data entry and ensuring data accuracy.

3. Customization and Extensibility

With Apps Script, you can customize a Google app to suit your specific business needs. You can create custom menus, dialogs, and sidebars providing tailored functionality and enhancing user experience.

4. Collaboration and Workflow Streamlining

Apps Script enables you to automate collaboration processes. For instance, in Google Docs you can automatically distribute documents to team members, trigger notifications or email alerts based on document changes, and establish approval workflows.

Use Case Walkthrough

Let us imagine you have a recurring meeting to review outstanding issues, identify the next steps to those issues, and track their progress. Typically, the list of open issues is manually collected ahead of the meeting, the discussion captured on a meeting note, the meeting note action items manually translated into issue tickets for tracking, and manually followed up on by the T/PgM.  

In this simple process, there are many areas for improvement, the most significant being the amount of manual intervention being high, which can lead to a high error rate.

Next, we will explore how Apps Script can streamline most of the steps involved in the process so that human effort is spent on high-value items. 

Creating a List of Open Issues

Apps Script has an HTTP client that can be implemented to call the API of an internal system. We can imagine the internal system being a ticket management system called ABC. We can write an app script function within Google Sheets that can call the ticket management system API to fetch and populate the rows of the Google Sheet document with the retrieved information. The code snippet below demonstrates how to make an API request to ABC using UrlFetchApp and populate the rows with the issue details:

function populateSheetWithAPIData() {

  // API endpoint URL for the internal system ABC

  var apiUrl = 'https://api.example.com/abc';

  

  // Make API request to fetch data from ABC service

  var response = UrlFetchApp.fetch(apiUrl); // Response from the passed Url

  var apiData = JSON.parse(response.getContentText()); // Parse the returned data

  

  // Get the active Google Sheet

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  

  // Clear existing data in the sheet to prepare for new entries

  sheet.getDataRange().clearContent();

  

  // Populate the sheet with API data

  var headers = Object.keys(apiData[0]);

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]); // Write headers

  sheet.getRange(2, 1, apiData.length, headers.length).setValues(apiData.map(Object.values)); // Write data rows

}

The main function here is populateSheetWithAPIData() where it calls the API of the internal system ABC by making an HTTP request to the specified apiUrl. The response from the API is then parsed as JSON and stored in the apiData variable.

The script assumes that you want to populate the data into the active sheet of the currently opened Google Sheet. It clears any existing content in the sheet using clearContent() and then proceeds to populate the sheet with the API data. The function writes the headers in the first row of the sheet and the data rows starting from the second row.

Instead of clearing the data before writing a new one, you can update the script to create a new tab in the active spreadsheet that corresponds with the current date every time the script is run. That is particularly helpful if you would like to keep a record of previous lists of issues that have been discussed.

function createTabAndPopulateSheetWithAPIData() {

  // API endpoint URL for the internal system ABC

  var apiUrl = 'https://api.example.com/abc';



  // Make API request to fetch data from ABC service

  var response = UrlFetchApp.fetch(apiUrl); // Response from the passed Url

  var apiData = JSON.parse(response.getContentText()); // Parse the returned data



  // Get the active Google Sheet

  var sheet = SpreadsheetApp.getActiveSpreadsheet();



  // Get the current date in a specific format

  var currentDate = Utilities.formatDate(new Date(), sheet.getSpreadsheetTimeZone(), 'yyyy-MM-dd');



  // Create a new sheet with the current date

  var newSheet = sheet.insertSheet(currentDate);



  // Populate the new sheet with API data

  var headers = Object.keys(apiData[0]);

  newSheet.getRange(1, 1, 1, headers.length).setValues([headers]); // Write headers

  newSheet.getRange(2, 1, apiData.length, headers.length).setValues(apiData.map(Object.values)); // Write data rows

}

Running the Script

To manually run this function, you can follow these steps:

1. Open your Google Sheet.
2. Go to "Extensions" in the menu and select "Apps Script."
3. Replace the existing code in the script editor with your script.
4. Save the script.
5. Close the script editor.
6. Run the function by going to "Extensions" and selecting "Apps Script" again. In the Apps Script dialog, choose "populateSheetWithAPIData" from the list of functions and click the "Run" button.

You can also have the function run automatically on a predetermined date and time and auto-populate the sheet by following a few small steps:

• Open your Google Sheet.
• Go to "Extensions" in the menu and select "Apps Script."
• In the Apps Script editor, click on the "Triggers" icon (clock-shaped) on the left sidebar.
• Click on the "+ Add Trigger" button at the bottom right of the Triggers page.
• Set up the trigger configuration:
• Choose the function to run: Select the function you want to trigger from the "Run" dropdown menu (e.g., createTabAndPopulateSheetWithAPIData).
• Choose which deployment should run.
• Select event source: Choose "Time-driven" from the dropdown menu.
• Select the type of time-based trigger: Choose the desired time-based trigger option, such as "Daytimer" or "Week timer."
• Select time interval: Set the specific date and time when you want the trigger to execute.
• Click on the "Save" button to create the trigger.

Review Open Tickets and Capture Meeting Notes

Let us face it; we would all prefer if we had fewer meetings and shorter ones for the important meetings. One thing we can do to speed up a meeting is to have an agenda and a template meeting note document. Since we are discussing app script, you can imagine the document is a Google Doc. 

So, by now, we have a Google Sheet that has the updated list of issues to be reviewed. Once the meeting commences, we can use the google doc meeting note template to capture discussions and record action items. As part of the template creation, Google Apps Script allows for the customization of the Google Docs toolbar by adding custom buttons. These buttons provide convenient access to automation functionalities, making it easier for users to execute automation scripts or perform specific tasks. For instance, if we add a button that reads “Send email” and is backed by an Apps Script logic, the meeting facilitator can click that button once the meeting finishes so that all participants get an email copy of the meeting note. Or even better, we can add another button that reads “File Tickets” so that the action items listed on the meeting note template can be used to automatically file tickets.

Here's a sample code snippet that demonstrates how to add a custom button to the Google Docs toolbar and adds a logic that will auto-file tickets once clicked:

function onOpen() {

  var ui = DocumentApp.getUi();



  // Create a menu in the toolbar

  var menu = ui.createMenu('Issue activity’);



  // Add a custom button to the menu

  menu.addItem('File Tickets', 'fileTickets');



  // Add the menu to the toolbar

  menu.addToUi();

}



function fileTickets() {

  // API endpoint URL for filing tickets in the internal system ABC

  var apiUrl = 'https://api.example.com/abc/tickets';



  // Sample payload for ticket creation

  var ticketData = {

    title: 'Sample Ticket',

    description: 'This is a sample ticket created via Apps Script',

    priority: 'High',

    // Add any other required ticket data

  };



  // Make API request to file tickets

  var options = {

    method: 'POST',

    contentType: 'application/json',

    payload: JSON.stringify(ticketData)

  };



  var response = UrlFetchApp.fetch(apiUrl, options);



  // Process the response or handle any errors accordingly

  if (response.getResponseCode() === 200) {

    // Show a dialog to inform the user that tickets have been filed successfully

    var ui = DocumentApp.getUi();

    ui.alert('Tickets have been filed in the internal ticket system ABC.');

  } else {

    // Handle error response

    var errorMessage = 'Error filing tickets. Status code: ' + response.getResponseCode();

    // You can parse the response content and extract specific error details if available

    // var errorDetails = JSON.parse(response.getContentText());

    // errorMessage += '. Error details: ' + errorDetails.message;



    var ui = DocumentApp.getUi();

    ui.alert(errorMessage);

  }

}

In the above snippet, the fileTickets() function contains the sample API request to file tickets in the internal ticket system ABC.

The ticketDataobject represents the payload for ticket creation. You can customize it according to the required fields and data structure of your ticketing system. You can add additional properties as needed.

The API request is made using UrlFetchApp.fetch(). It includes the necessary options, such as the request method (POST), content type (application/json), and the payload in JSON format.

The response from the API request is processed to check the response code. If the response code is 200 (indicating a successful request), a dialog is displayed to inform the user that tickets have been filed successfully.

In case of an error response, an error message is displayed in the dialog. You can customize the error handling logic to extract and display specific error details if available in the API response.

Tracking Open Issues

After the tickets have been filed, T/PgMs will typically track the status of the tickets to provide updates on their progress. It is intractable to follow the manual process if there are hundreds of open issues. Thus, we can automate that process by extending our script in Google Sheets to provide an update whenever the value of the cell changes. The following is a sample code for sending an email to stakeholders whenever the status column of the spreadsheet changes:

function sendEmailOnStatusChange(e) {

  var sheet = e.source.getActiveSheet();

  var columnToWatch = 3; // Change this to the column number you want to monitor (e.g., column C is 3)



  // Check if the edited cell is in the desired column

  if (e.range.getColumn() === columnToWatch) {

    var editedValue = e.range.getValue();

    var editedRow = e.range.getRow();



    // Check the value in the edited cell to determine if an email should be sent

    if (editedValue === 'Complete') {

      var recipient = 'recipient@example.com'; // Change this to the email address of the recipient

      var subject = 'Status Update: Task Completed';

      var message = 'Task in row ' + editedRow + ' has been marked as complete.';



      // Send the email

      MailApp.sendEmail(recipient, subject, message);

    }

  }

}

Conclusion

Google Apps Script provides a powerful and flexible platform for automating business processes within Google Workspace Apps. By leveraging its capabilities, you can save time, streamline workflows, and enhance collaboration. The code snippet provided serves as a starting point for automating common workflows, and you can build upon it to meet your specific automation needs. With Google Apps Script, you can unlock the full potential of Google workspace apps for efficient and customized business process automation.

Google Apps Script offers various methods and libraries that allow developers to make HTTP requests to APIs and fetch data. This functionality can be utilized to automate processes in applications such as Google Docs by integrating with external systems, retrieving information, and performing actions based on the retrieved data.