Implementing Google Services RPA Automation with Google Apps Script

Implementing Google Services RPA Automation with Google Apps Script

Implementing Robotic Process Automation for Google Workspace services using Google Apps Script

Photo by Possessed Photography

Robotic Process Automation

RPA (Robotic Process Automation) translates to “process automation robots” in Chinese. Looking back at human history, from hand-gathering to the Stone Age, then to agricultural civilization, from the industrial revolution of the last century to the information boom of the past 20 years, human work efficiency and productivity have grown exponentially. Along the way, RPA applications have been ubiquitous, such as waterwheels in the agricultural era (automated threshing work), textile machines in the industrial revolution (automated textile work), factory robotic arms (automated assembly work), and finally, the automated information-related work introduced in this article, such as automatic report queries, automatic notifications, and so on.

Embarrassingly, I only recently learned this term. Since my first job (7 years ago), I have been doing RPA-related work, such as writing crawlers to collect statistics, automating CI/CD processes, automating data queries, automating stability data alerts, and automating daily routine operations. However, I used to refer to it simply as “automation.” It’s time to give it a proper name — RPA (Robotic Process Automation).

Previously, my RPA efforts focused more on “writing code to automate tasks to solve single problems,” lacking comprehensive preliminary evaluation and analysis, the use of No/Low Code tools, regulations, operational monitoring, actual data statistics, continuous improvement, corporate culture promotion, and so on. These are all essential aspects of complete RPA. However, as mentioned earlier, I only recently learned about this professional field, so let me start with a practical article!

There are many platforms providing RPA services, such as Automation Anywhere, UiPath, Microsoft Power Automate, Blue Prism, or Zapier, IFTTT, Automate.io. You need to choose the appropriate service based on the actual problem you want to solve and the platform.

I recommend a free open-source browser-based RPA tool: Automa.

Broadly speaking, transforming the active dependence between people or between people and tasks into dependence on platforms is also a form of RPA.

For example: using project management tools like Asana/Jira to manage work tasks uniformly.

Based on the concept of transforming active to passive, we can also implement an RPA for services that originally required manual checks for new notifications, automatically notifying us when there are new changes.

For example: The previously implemented Gmail to Slack forwards specific notification emails to the work group.

Evaluation of the Benefits of Robotic Process Automation

Previously, in the “2021 Pinkoi Tech Career Talk — Unveiling the Secrets of a Highly Efficient Engineering Team”, we discussed the costs of small accumulations and interruptions in flow; assuming a routine repetitive task takes 15 minutes to solve each time, occurs 10 times a week, and wastes nearly 130 hours a year; if we also consider the cost of “context switching,” it could ultimately waste nearly 200 hours a year.

2021 Pinkoi Tech Career Talk — Unveiling the Secrets of a Highly Efficient Engineering Team

Context switching means that when we are highly focused on important tasks, we need to pause to handle other matters, and the time it takes to get back into the state after handling them.

The benefits evaluation of developing RPA can refer to the figure below. As long as the development time required and the frequency encountered are greater than the time wasted, it is worth investing resources to implement:

https://twitter.com/swyx/status/1196401158744502272

• X-axis: Task frequency ex: 50/Day (50 times a day)
• Y-axis: How much manpower time is required to complete the task each time
• Time cost range is calculated over 5 years, the middle of the table indicates the manpower cost wasted over 5 years
• White indicates that the time cost of automation is greater than the benefits obtained, not worth improving
• Green indicates items worth automating
• Red strongly suggests converting to automation

In addition to saving time, automated standardized processes can also reduce the chance of human error and improve stability.

The Relationship Between Robotic Process Automation and AI

With the rise of AI, RPA is also frequently mentioned; but I think RPA has no direct relationship with AI, RPA existed long before the era of AI, and the benefits of AI adoption in enterprises may not be as high as the benefits of perfecting RPA. RPA is more about corporate culture and work habits; however, it is undeniable that AI can indeed help RPA reach the next level. For example, RPA used to only handle precise, routine tasks, but with AI, it can handle some fuzzy, more dynamic, and intelligent judgment tasks.

Robotic Process Automation at Google Workspace

Google Workspace (formerly G Suite) is our daily office collaboration partner. We use Gmail for email hosting, Google Docs for documents, Google Sheets for spreadsheets, Google Forms for forms, etc. The integration between these services or communication with internal and external systems requires us to implement RPA to complete.

However, Google does not provide direct RPA services, which can be achieved through the following services:

• No Code: App Sheet (paid service), allows non-developers to directly build service integration automation through GUI.
• Low Code: Google Apps Script (free service), allows quick and direct bridging of Google services, external/internal systems with simple programming.
• Function as a Service: Cloud Functions (paid service with free tier), allows writing complete code and services, deployed and executed directly through Google Cloud.

I haven’t used the No Code platform App Sheet, but I have quite a bit of experience with Cloud Functions and Google Apps Script. Here are some personal experiences and choices:

Cloud Functions

• Requires deployment to execute
• Supports multiple programming languages: Node.js, Python, Java, Go, PHP, Ruby…
• Supports third-party package dependency management, installation, and usage
• Supports complete authentication mechanisms
• Maximum execution time limit: 60 minutes
• Pay-as-you-go: charged based on the number of invocations, execution time, different processors, and memory used
• Limited by cold start issues (if not called for a long time, the first call will take longer response time)
• Cannot directly integrate with Google services, needs to go through Auth/API authentication
• Free tier as follows Cloud Functions offers a perpetual free tier for compute time resources, including allocations of GB-seconds and GHz-seconds. In addition to 2 million invocations, this free tier also provides 400,000 GB-seconds and 200,000 GHz-seconds of compute time, as well as 5 GB of internet data transfer per month. The free tier usage is calculated in equivalent USD amounts at the Tier 1 pricing level. Regardless of whether the function execution region uses Tier 1 and/or Tier 2 pricing, the equivalent USD amount will be allocated to you. However, when deducting the free tier quota, the system will be based on the function execution region’s tier (Tier 1 or Tier 2). Please note that even if you are using the free tier, you must have a valid billing account.

In summary, Cloud Functions are recommended when more comprehensive and complex RPA integration functions or more external API integration needs are required.

Previous cases using Cloud Functions include:

• Slack ChatGPT Conversation Bot
• Automatic Check-in Bot

I use it when integrating with non-Google Workspace services and bridging other external services.

Google Apps Script

• Convenient, simple, and fast
• Completely free
• No cumbersome and complex Auth authentication required for service integration (Google Apps Script uses the currently executing account as the execution identity)
• Built-in scheduling and calendar trigger functions
• Use Google network to execute network requests
• Can only use Google Apps Script (based on JavaScript) for development
• Does not support package management tools, no version control function
• Due to security issues, customizing Request User-Agent information is not possible
• Execution time limit, the script must complete the work within 6 minutes, otherwise, it will be terminated.
• For other restrictions and quotas, please refer to the official GAS information:

Previous cases using Google Apps Script include:

• Integrating Slack x Google Form x Google Sheet
• Integrating Slack x Gmail
• Integrating Google Analytics x Slack
• Integrating Firebase Crashlytics x Big Query x Slack
• Integrating Github x LineBot

Due to execution time and API Request customization limitations, I only use Google Apps Script for simple and quick services; or when there is a need to integrate with Google services, I will prioritize using Google Apps Script (because using Cloud Functions requires implementing a complete Google service authentication process).

Robotic Process Automation with Google Apps Script — Work Daily Report (Google Sheet x Google Analytics)

Finally, we come to the topic of this article, using Google Apps Script to achieve Google service RPA automation.

Background

The product team needs to query Google Analytics data daily and fill it into the Google Sheet data report for team trend analysis; and publish the daily data content to the Dashboard screen so that all members can grasp the current situation.

Colleagues need to spend about 30 minutes to complete this task every day when they arrive at the company; if there are other things to deal with, they need to wait until this routine work is completed or delay the release of daily data messages.

Simple estimation of RPA benefits:

• Annual consumption expenditure: 1 person x 30 mins x 365 days (holiday data also needs to be supplemented) = 182 hours
• Automation setup cost: In this case, it takes about 1 person x 5 days = 40 hours

Therefore, we only need to invest one week of development time to solve the workload of the colleague responsible for data checking in the long run, allowing them to focus on more important tasks.

Goal

Our goal is to use Google Apps Script to create an RPA that automatically retrieves daily data from Google Analytics and internal system report APIs and fills it into Google Sheets, as well as setting up a Web UI Dashboard.

Final Effect

• Google Sheet: https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit?usp=sharing

• Web GUI URL: https://script.google.com/macros/s/AKfycbz2Vk-ikU8DSXjpnLq9r6HNAn3zlNAosvDoItG0cxy0bmItRDSVyEzTdwsL2HyFUz99/exec

The data is fake, purely for demo use; from 2024/04/13 onwards, it will be particularly low or remain at 0 because my zhgchg.li GA really has “0” traffic Q_Q.

Tasks to Complete

1. Create Google Apps Script, familiarize with the editor
2. Obtain/create the corresponding date Sheet
3. Connect to Google Analytics to retrieve data
4. Populate data
5. Set up scheduling for daily automatic execution

Disclaimer

For the sake of explanation, the following code will be as less abstract as possible and more explanatory. You can modify it according to your actual needs.

A complete public Google Sheet & Google Apps Script is attached at the end of the article. If you are too lazy to follow step by step, you can directly modify the template provided at the end.

Step 1. Create Google Apps Script

Simply select “Extensions” -> “Apps Script” on the report we want to automate to automatically create a Google Apps Script linked to the Google Sheet report.

Alternatively, you can directly create it from the Google Apps Script homepage Google Apps Script, but this will not link to the Google Sheet.

It is not necessary to link to operate the corresponding Google Sheet, both methods can be used. The difference lies in the ownership of the Script. If it is linked to the report, it belongs to the report owner; if created by yourself, it belongs to the creator. Ownership will affect whether the script will be invalidated or deleted if the account is deactivated due to resignation.

After creating the script, we can first rename our script project from the top.

Google Apps Script Basics

Before moving on to the next step of writing the program, let’s supplement some basic knowledge of Google Apps Script.

About the Editor

The SDK for Google services is introduced by default (no special introduction is required to call and use):

• CalendarApp Calendar
• DocumentApp Google Drive
• FormApp Google Form
• SpreadsheetApp Google Sheet
• GmailApp Gmail
• Others…

1. File: You can add multiple .gs files to store different object codes for better organization; all files will execute under the same Namespace and lifecycle, so be careful as object names and variable names may overwrite each other if duplicated. In addition to .gs script files, you can also add .html HTML Template files for rendering Web UI. (This will be introduced later)
2. Library: Libraries written by others (a.k.a Lib) can be imported using their Script ID. Of course, the scripts we write can also be deployed as libraries for others to use. There are also some tools packaged by experts that can be used, but the downside is that you can only search for Script IDs via Google, as there is no official library list for reference. e.g. HTML Parser Tool Cheer.io Script ID: 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0
3. Services: SDKs for Google services. Services not included by default can be added here. e.g. Google Analytics Data
4. Undo, Redo
5. Save or Control + s
6. Run or Control + r Errors will be directly prompted in the Console and the script will terminate.
7. Debug Execution will pause and the right-side Debug View will pop up when it hits a Break point (10). You can then continue execution. Errors will pause execution and the right-side Debug View will pop up.
8. Target method for debugging and execution (Function Name) Only methods in the currently selected file can be chosen.
9. View editor execution logs

Another point to note is indentation. In some browsers, pressing “Control + [” to indent will trigger the back page action, so be careful!

Google Apps Script GitHub Assistant Chrome Extension

• It is recommended to install this Extension to connect Google Apps Script with git, enabling version control to prevent accidental changes.

• If you encounter Push/Pull Errors or no response when clicking, please follow the steps above: “Options” -> Connect to Github or re-authenticate Google authorization.

Logger Message

You can use the following script with Debug to print Debug Logs in the Console.

Logger.log("Hi")

Execution Logs and Error Information

Logs or errors during execution in the editor will be displayed directly. To check execution logs or errors during automatic execution, go to the “Executions” tab.

Automatic Triggers

The “Triggers” tab allows you to set how methods in the script are automatically triggered. The automatic trigger conditions that can be set include:

• When Google Sheet: opens, edits, content changes, form submissions
• Scheduled triggers: every X minutes, X hours, X days, X weeks, X months
• Specific date triggers: YYYY-MM-DD HH:MM
• When Calendar: updates

Error notification settings can be configured to notify you when the script execution fails.

Grant Execution Permissions

The first execution/deployment or adding new services/resources will require re-authorization. Subsequent executions will use the authorized identity, so ensure that the authorized (usually current) account has the necessary permissions for the resources/services (e.g., Google Sheet permissions).

After the account selection pop-up appears, choose the account to authorize for execution (usually the current Google Apps Script account):

The message “Google hasn’t verified this app” appears because the app we are developing is for personal use and does not need to be verified by Google.

Simply click “Advanced” -> “Go to XXX (unsafe)” -> “Allow”:

After completing the authorization, you can successfully run the script. If there are no changes to the resources, re-authorization is not required.

2. Obtain/Create the Sheet for the Corresponding Date

After understanding the basic knowledge, we can write the program for the first function.

We create the following multiple files to store different objects:

DailyReportStyle.gs field style object:

class HeaderStyle {
  constructor() {
    this.color = "#ffffff";
    this.backgroundColor = "#e3284b";
    this.bold = false;
    this.size = 12;
    this.horizontalAlignment = "center";
    this.verticalAlignment = "middle";
  }
}

class ContentStyle {
  constructor() {
    this.color = "#000000";
    this.backgroundColor = "#ffffff";
    this.bold = false;
    this.size = 12;
    this.horizontalAlignment = "center";
    this.verticalAlignment = "middle";
  }
}

class HeaderDateStyle {
  constructor() {
    this.color = "#ffffff";
    this.backgroundColor = "#001a40";
    this.bold = true;
    this.size = 12;
    this.horizontalAlignment = "center";
    this.verticalAlignment = "middle";
  }
}

DailyReportField.gs field data object:

class DailyReportField {
  constructor(name, headerStyle, contentStyle, format = null, value = null) {
    this.name = name;
    this.headerStyle = headerStyle;
    this.contentStyle = contentStyle;
    this.format = format;
    this.value = value;
  }
}

DailyReport.gs main report program logic:

class DailyReport {
  constructor(sheetID, date) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.date = date;

    this.sheetFields = [
      new DailyReportField("Date", new HeaderDateStyle(), new HeaderDateStyle()),
      new DailyReportField("Day of the Week", new HeaderDateStyle(), new HeaderDateStyle()),
      new DailyReportField("Daily Traffic", new HeaderStyle(), new ContentStyle(), "#,##0", '=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&4)+INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&5)'), // =4(PC Traffic) + 5(Mobile Traffic)
      new DailyReportField("PC Traffic", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("Mobile Traffic", new HeaderStyle(), new ContentStyle(), "#,##0"),
      new DailyReportField("Registrations", new HeaderStyle(), new ContentStyle(), "#,##0")
    ]

    // Explanation of the daily traffic formula:
    // 1. The COLUMN() function returns the column number of the current cell.
    // 2. ADDRESS(1, COLUMN(), 4) generates an absolute reference address with the given row number (result of `COLUMN()`) and fixed column number (1). The third parameter 4 indicates a relative address without any dollar signs ($). For example, if you use this function in any cell in the third column, it will return "C1".
    // 3. SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") removes the number 1 from the address generated by the ADDRESS function, leaving only the column letter, e.g., "C".
    // 4. INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 4) here & 4 should actually be &4. The result of the `SUBSTITUTE` function is concatenated with the number 4, forming a string like "C4", and then the INDIRECT function converts this string into the corresponding cell reference. So, if you use this formula in any cell in column C, it will reference C4.
    // 5. Similarly, `INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 5)` references the cell in the fifth row of the same column. For example, if you use this formula in any cell in column C, it will reference C5.
    // 6. Finally, the values of the cells referenced by these two INDIRECT functions are added together.
  }

  execute() {
    const sheet = this.getSheet();

  }

  // Get the target Sheet for the given date
  getSheet() {
    // Distinguish Sheets by month, find the current month's Sheet
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // If not found, create a new monthly Sheet
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // Monthly Sheet naming convention
  getSheetName() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM");
  }

  // Create a new monthly Sheet
  makeMonthSheet() {
    // Add the current month's Sheet, move it to the first position
    var thisMonthSheet = this.separateSheet.insertSheet(this.getSheetName(), {index: 0});
    thisMonthSheet.activate();
    this.separateSheet.moveActiveSheet(1);

    // Add the first column, field names, set Pinned, width 200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // Fill in the field names
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const text = sheetField.name;
      const style = sheetField.headerStyle;
      
      const range = thisMonthSheet.getRange(parseInt(currentRow) + 1, 1);
      this.setContent(range, text, style);
      range.setHorizontalAlignment("left");
    }

    // Set row heights
    thisMonthSheet.setRowHeights(1, Object.keys(this.sheetFields).length, 30);

    // Set Pinned for the first and second rows (Date, Day of the Week)
    thisMonthSheet.setFrozenRows(2);

    // Add a summary column
    thisMonthSheet.insertColumnsAfter(thisMonthSheet.getLastColumn(), 1); // Add one column after the last column
    const summaryColumnIndex = thisMonthSheet.getLastColumn() + 1;

    // Fill in the summary column
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const summaryRowIndex = parseInt(currentRow) + 1;

      const range = thisMonthSheet.getRange(summaryRowIndex, summaryColumnIndex);
      const style = sheetField.contentStyle;

      if (summaryRowIndex == 1) {
        // Date...
        this.setContent(range, "Total", style);
      } else if (summaryRowIndex == 2) {
        // Day of the Week...merge...
        const mergeRange = thisMonthSheet.getRange(1, summaryColumnIndex, summaryRowIndex, 1);
        this.setContent(mergeRange, "Total", style);
        mergeRange.merge();
      } else {
        this.setContent(range, '=IFERROR(SUM(INDIRECT(SUBSTITUTE(ADDRESS(1, 1, 4), "1", "") & '+summaryRowIndex+'):INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN() - 1, 4), "1", "") & '+summaryRowIndex+')), 0)', style);

        // 1. The IFERROR(value, [value_if_error]) function is used to check if there is an error in the formula and return a specified value if there is an error. It takes two parameters: `value` is the expression or function to be calculated, and `value_if_error` is the value returned when value has an error. In this context, if the calculation in the SUM function has an error, it returns 0.
        // 2. The SUM(range) function is used to calculate the sum of all numbers in the range.
        // 3. The INDIRECT(ref_text, [is_A1_notation]) function converts a text string into a cell reference. Here, the INDIRECT function is used to dynamically generate the required reference range.
        // 4. The SUBSTITUTE(text, old_text, new_text, [instance_num]) function replaces specified text in a text string. Here, SUBSTITUTE is used to replace the "1" in the address returned by the ADDRESS function with other content.
        // 5. The ADDRESS(row, column, [abs_num], [a1], [sheet]) function returns the cell address corresponding to the given row and column numbers. Here, ADDRESS(1, 1, 4) generates the cell address of the first row and first column, but since abs_num is 4, the address does not include the worksheet name and fixed symbol $. Similarly, `ADDRESS(1, COLUMN() - 1, 4)` generates the cell address from the first row to the previous column of the current column.
        // 6. The COLUMN() function returns the column number of the current cell.
        // 7. summaryRowIndex = the row number
      }
    }

    return thisMonthSheet;
  }

  setContent(range, text, style) {
    if (String(text) != "") {
      range.setValue(text);
    }

    range.setBackgroundColor(style.backgroundColor);
    range.setFontColor(style.color);

    if (style.bold) {
      range.setFontWeight("bold");
    }

    range.setHorizontalAlignment(style.horizontalAlignment);
    range.setVerticalAlignment(style.verticalAlignment);
    range.setFontSize(style.size);
    range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  }
}

Main.gs as the main program entry point:

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE"
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

function debug() {
  var report = new DailyReport(targetGoogleSheetID, new Date());
  report.execute();
}

After completion, we return to Main.gs, select “debug” and press debug to check if the execution result is correct and if there are any errors.

If executed correctly, the report will show the current new month, with default fields and total fields. If it already exists, there will be no response.

3. Integrate Google Analytics to fetch data

First, you need to add the “AnalyticsData” service:

Use the GA4 Debug Tool to construct query conditions:

• https://ga-dev-tools.google/ga4/query-explorer/
• You can compare and construct the filtering conditions in the GA 4 backend
• This article uses querying the number of Sessions as an example, distinguishing device Grouping

Log in and authorize, then select the target resource:

Note down the number displayed under the property, which is the GA Property ID you want to query.

Set query parameters and Filter conditions:

Press “Make Request” to get the Response result:

You can simultaneously compare the data with the same conditions in the GA 4 backend to see if they match. If there is a significant difference, it might be because some Filter conditions were not added, so you need to check again.

Note

A small pitfall discovered by a marketing colleague: some GA data may have delay issues, meaning the numbers you check today might be different from those you checked yesterday (e.g., bounce rate). Therefore, it’s best to backtrack the data a few days to ensure the final numbers are accurate.

After confirming that the GA Debug Tool is working correctly, we can convert it into Google Apps Script.

Add a new GAData.gs file:

// Remember to add Google Analytics Data API to Services, or you'll see this error: ReferenceError: AnalyticsData is not defined
// GA Debug Tool: https://ga-dev-tools.web.app/ga4/query-explorer/

class GAData {
  constructor(date) {
    this.date = date;

    const traffic = this.fetchGADailyUsage();
    this.pc_traffic = traffic["desktop"];
    this.mobile_traffic = traffic["mobile"];
  }

  fetchGADailyUsage() {
    const dimensionPlatform = AnalyticsData.newDimension();
    dimensionPlatform.name = "deviceCategory";

    const metric = AnalyticsData.newMetric();
    metric.name = "sessions";

    const dateRange = AnalyticsData.newDateRange();
    // Default query for data within the given date range e.g. 2024-01-01 ~ 2024-01-01
    dateRange.startDate = this.getDateString();
    dateRange.endDate = this.getDateString();

    // Filter Example:
    // const filterExpression = AnalyticsData.newFilterExpression();
    // const filter = AnalyticsData.newFilter();
    // filter.fieldName = "landingPagePlusQueryString";
    // const stringFilter = AnalyticsData.newStringFilter()
    // stringFilter.value = "/life|/article|/chat|/house|/event/230502|/event/230310";
    // stringFilter.matchType = "PARTIAL_REGEXP";
    // filter.stringFilter = stringFilter;
    // filterExpression.filter = filter;

    const request = AnalyticsData.newRunReportRequest();
    request.dimensions = [dimensionPlatform];
    request.metrics = [metric];
    request.dateRanges = dateRange;

    // Filter Example:
    // const filterExpression = AnalyticsData.newFilterExpression();
    // filterExpression.expression = filterExpression;
    // request.dimensionFilter = filterExpression;
    // or Not
    // const notFilterExpression = AnalyticsData.newFilterExpression();
    // notFilterExpression.notExpression = filterExpression;
    // request.dimensionFilter = notFilterExpression;

    const report = AnalyticsData.Properties.runReport(request, "properties/" + gaPropertyId).rows;
    // No data
    if (report == undefined) {
      return {"desktop": 0, "mobile": 0};
    }

    // [{metricValues=[{value=4517}], dimensionValues=[{value=mobile}]}, {metricValues=[{value=3189}], dimensionValues=[{value=desktop}]}, {metricValues=[{value=63}], dimensionValues=[{value=tablet}]}]

    var result = {};
    report.forEach(function(element) {
      result[element.dimensionValues[0].value] = element.metricValues[0].value;
    });

    return result;
  }

  getDateString() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM-dd");
  }
}

Main.gs Add test content:

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  var report = new DailyReport(targetGoogleSheetID, new Date());
  report.execute();
  //
  var gaData = new GAData(new Date());
  Logger.log(gaData);
}

Press run or debug to get the program fetch result:

OK! The comparison matches.

When this step is completed, the directory file structure is as shown above.

4. Fill in the data

After creating the Sheet and checking the data, the next step is to fill in the data into the fields.

Adjust DailyReport.gs to add logic for adding fields & filling data by date:

class DailyReport {
  constructor(sheetID, date, gaData, inHouseReportData) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.date = date;

    const dateString = Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd");
    const weekString = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"][date.getDay()]; // Get the day of the week, Sunday is 0, Monday is 1, and so on

    this.sheetFields = [
      new DailyReportField("Date", new HeaderDateStyle(), new HeaderDateStyle(), null, dateString),
      new DailyReportField("Day", new HeaderDateStyle(), new HeaderDateStyle(), null, weekString),
      new DailyReportField("Daily Traffic", new HeaderStyle(), new ContentStyle(), "#,##0", '=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&4)+INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&5)'), // =4(PC Traffic) + 5(Mobile Traffic)
      new DailyReportField("PC Traffic", new HeaderStyle(), new ContentStyle(), "#,##0", gaData.pc_traffic),
      new DailyReportField("Mobile Traffic", new HeaderStyle(), new ContentStyle(), "#,##0", gaData.mobile_traffic),
      new DailyReportField("Registrations", new HeaderStyle(), new ContentStyle(), "#,##0", inHouseReportData.registers)
    ]
  }

  execute() {
    const sheet = this.getSheet();
    const dateColumnIndex = this.makeOrGetDateColumn(sheet); // Get the existing update or create a new field

    // Fill in the field content
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const rowIndex = parseInt(currentRow) + 1;

      if (rowIndex != null) {
        const range = sheet.getRange(rowIndex, dateColumnIndex);
        const text = sheetField.value;
        const style = sheetField.contentStyle;
        this.setContent(range, text, style);
        this.setFormat(range, sheetField.format);          
      }
    }
  }

  // Get the target Sheet for the given date
  getSheet() {
    // Distinguish Sheets by month, find the current month's Sheet
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // If not found, create a new month Sheet
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // Month Sheet naming
  getSheetName() {
    return Utilities.formatDate(this.date, "GMT+8", "yyyy-MM");
  }

  // Create a new month Sheet
  makeMonthSheet() {
    // Add the current month's Sheet, move to the first position
    var thisMonthSheet = this.separateSheet.insertSheet(this.getSheetName(), {index: 0});
    thisMonthSheet.activate();
    this.separateSheet.moveActiveSheet(1);

    // Add the first column, field name, set Pinned, width 200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // Fill in the field names
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const text = sheetField.name;
      const style = sheetField.headerStyle;
      
      const range = thisMonthSheet.getRange(parseInt(currentRow) + 1, 1);
      this.setContent(range, text, style);
      range.setHorizontalAlignment("left");
    }

    // Set row height
    thisMonthSheet.setRowHeights(1, Object.keys(this.sheetFields).length, 30);

    // Set Pinned for the first and second rows (Date, Day)
    thisMonthSheet.setFrozenRows(2);

    // Add total field
    thisMonthSheet.insertColumnsAfter(thisMonthSheet.getLastColumn(), 1); // Add a column after the last column
    const summaryColumnIndex = thisMonthSheet.getLastColumn() + 1;

    // Fill in the total field
    for(const currentRow in this.sheetFields) {
      const sheetField = this.sheetFields[currentRow];
      const summaryRowIndex = parseInt(currentRow) + 1;

      const range = thisMonthSheet.getRange(summaryRowIndex, summaryColumnIndex);
      const style = sheetField.contentStyle;

      if (summaryRowIndex == 1) {
        // Date...
        this.setContent(range, "Total", style);
      } else if (summaryRowIndex == 2) {
        // Day...merge...
        const mergeRange = thisMonthSheet.getRange(1, summaryColumnIndex, summaryRowIndex, 1);
        this.setContent(mergeRange, "Total", style);
        mergeRange.merge();
      } else {
        this.setContent(range, '=IFERROR(SUM(INDIRECT(SUBSTITUTE(ADDRESS(1, 1, 4), "1", "") & '+summaryRowIndex+'):INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN() - 1, 4), "1", "") & '+summaryRowIndex+')), 0)', style);
      }
    }

    return thisMonthSheet;
  }

  // Create or get the date field
  // Add a field from the most recent day
  makeOrGetDateColumn(sheet) {
    const firstRowColumnsRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // Get the data range of the first row (date)
    const firstRowColumns = firstRowColumnsRange.getValues()[0]; // Get the values of the data range, 0 = first row
    
    var columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(this.date, "GMT+8", "yyyy/MM/dd"))); // Find the index of the corresponding date field

    if (columnIndex < 0) {
      // Not Found, find the position of the previous day
      var preDate = new Date(this.date);
      preDate.setDate(preDate.getDate() - 1);

      while(preDate.getMonth() == this.date.getMonth()) {
        columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(preDate, "GMT+8", "yyyy/MM/dd")));
        if (columnIndex >= 0) {
          break;
        }

        preDate.setDate(preDate.getDate() - 1);
      }

      if (columnIndex >= 0) {
        columnIndex += 1;
        sheet.insertColumnsAfter(columnIndex, 1); // Add a column after the previous day's field
        columnIndex += 1;
      }
    } else {
      columnIndex += 1;
    }

    if (columnIndex < 0) {
        sheet.insertColumnsAfter(1, 1); // Default, directly add a column after the first column
        columnIndex = 2;
    } 

    // Set column width
    sheet.setColumnWidths(columnIndex , 1, 100);

    return columnIndex
  }

  // Set field format style
  setFormat(range, format) {
    if (format != null) {
      range.setNumberFormat(format);
    }
  }

  // Fill content into the field
  setContent(range, text, style) {
    if (String(text) != "") {
      range.setValue(text);
    }

    range.setBackgroundColor(style.backgroundColor);
    range.setFontColor(style.color);

    if (style.bold) {
      range.setFontWeight("bold");
    }

    range.setHorizontalAlignment(style.horizontalAlignment);
    range.setVerticalAlignment(style.verticalAlignment);
    range.setFontSize(style.size);
    range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  }
}

Adjust Main.gs to add data integration and assign values during the build phase:

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  const date = new Date();
  const gaData = new GAData(date);
  const inHouseReportData = fetchInHouseReportData(date);
  
  const report = new DailyReport(targetGoogleSheetID, date, gaData, inHouseReportData);
  report.execute();
  
}

// Simulate some data that might be obtained by hitting other platform APIs.
function fetchInHouseReportData(date) {
  // EXAMPLE REQUEST:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // OR
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // REMEMBER, DUE TO SECURITY REASON, We can't customize user-agent.
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // MOCK DATA random 30~180
}

After completion, go back to Main.gs, select debug, and press debug to check if the execution result is correct and if there are any errors.

Back to Google Sheet! Success! We have successfully added the data for the date automatically.

5. Set up a schedule for daily automatic execution

After completing the script, just set up the automatic trigger conditions to complete it automatically every day.

Adjust Main.gs to add the cronjob() function:

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  cronjob();
}

// In reality, it is usually the data from yesterday that is checked today for complete data.
function cronjob() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const gaData = new GAData(yesterday);
  const inHouseReportData = fetchInHouseReportData(yesterday);
  
  const report = new DailyReport(targetGoogleSheetID, yesterday, gaData, inHouseReportData);
  report.execute();
}

// Simulate some data that might be obtained by hitting other platform APIs.
function fetchInHouseReportData(date) {
  // EXAMPLE REQUEST:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // OR
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // REMEMBER, DUE TO SECURITY REASON, We can't customize user-agent.
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // MOCK DATA random 30~180
}

Switch to the “Triggers” tab in the editor and select “Add Trigger” in the bottom right corner:

• Select the function you want to execute: the newly added Main.gs Function cronjob
• Select the deployment to execute: Head (latest version)
• Select the event source: Time-driven
• Select the type of time-based trigger: Day timer
• Select the time period: AM 4:00 — AM 5:00 (GMT+08:00) Usually, it will execute as soon as it hits AM 4:00.
• Error notification settings: Whether to notify immediately when the script encounters an error or to summarize it daily

Save the settings, and you’re done.

You can then go to the “Executions” tab to check the execution record results:

At this point, we have completed the RPA function for automating queries, adding data, and filling in data reports. 🎉🎉🎉

Setting Up a Web GUI Dashboard

Next, there is a secondary requirement. We need to create a simple web display of daily data (similar to a war room concept) that will be directly displayed on a large screen on the wall behind the team.

The effect is as shown below:

Add Web_DailyReport.gs to read Google Sheets and convert the columns and styles to HTML format for display:

class WebDailyReport {
  constructor(sheetID, dayCount) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.dayCount = dayCount;
    this.sheetRows = [
      "Date",
      "Day of the Week",
      "Daily Traffic",
      "PC Traffic",
      "Mobile Traffic",
      "Registration Count"
    ];
  }

  allData(startDate) {
    var sheetRowsIndexs = {};
    var count = this.dayCount;
    var result = [];
    while (count >= 0) {
      const preDate = new Date(startDate);
      preDate.setDate(preDate.getDate() - (this.dayCount - count));
      const sheetName = Utilities.formatDate(preDate, "GMT+8", "yyyy-MM");
      const targetSheet = this.separateSheet.getSheetByName(sheetName);
      if (targetSheet != null) {
        const firstRowColumnsRange = targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()); // Get the range of the first row (date)
        const firstRowColumns = firstRowColumnsRange.getValues()[0]; // Get the values of the range, 0 = first row
        var columnIndex = firstRowColumns.findIndex((date) => (date instanceof Date && Utilities.formatDate(date, "GMT+8", "yyyy/MM/dd") == Utilities.formatDate(preDate, "GMT+8", "yyyy/MM/dd"))); // Find the index of the corresponding date column
        
        if (columnIndex >= 0) {
          columnIndex = parseInt(columnIndex) + 1;
          if (sheetRowsIndexs[sheetName] == undefined || sheetRowsIndexs[sheetName] == null) {
            sheetRowsIndexs[sheetName] = this.sheetRows.map((sheetRow) => this.getFieldRow(targetSheet, sheetRow));
          }

          if (result.length == 0) {
            // Add the first column
            const ranges = sheetRowsIndexs[sheetName].map((rowIndex) => (rowIndex != null) ? (targetSheet.getRange(rowIndex, 1)) : (null));
            result.push(this.makeValues(ranges));
          }

          const ranges = sheetRowsIndexs[sheetName].map((rowIndex) => (rowIndex != null) ? (targetSheet.getRange(rowIndex, columnIndex)) : (null));
          result.push(this.makeValues(ranges));
        }
      }

      count -= 1;
    }

    var transformResult = {};
    for (const columnIndex in result) {
      for (const rowIndex in result[columnIndex]) {
        if (transformResult[rowIndex] == undefined) {
          transformResult[rowIndex] = [];
        }

        if (columnIndex == 0) {
          transformResult[rowIndex].unshift(result[columnIndex][rowIndex]);
        } else {
          transformResult[rowIndex].splice(1, 0, result[columnIndex][rowIndex]);
        }
        
      }
    }

    return transformResult;
  }

  // Convert field attributes to display objects
  makeValues(ranges) {
    const data = ranges.map((range) => (range != null) ? (range.getDisplayValues()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const backgroundColors = ranges.map((range) => (range != null) ? (range.getBackgrounds()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const colors = ranges.map((range) => (range != null) ? (range.getFontColorObjects()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const sizes = ranges.map((range) => (range != null) ? (range.getFontSizes()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const bolds = ranges.map((range) => (range != null) ? (range.getFontWeights()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const horizontalAlignments = ranges.map((range) => (range != null) ? (range.getHorizontalAlignments()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));
    const verticalAlignments = ranges.map((range) => (range != null) ? (range.getVerticalAlignments()) : (null)).map((values) => (values != null) ? (values[0][0]) : (null));

    var result = [];
    for(const index in data) {
        const row = data[index];
        result.push({
          "value": row,
          "backgroundColor": backgroundColors[index],
          "color": this.colorStripper(colors[index]?.asRgbColor()?.asHexString()),
          "size": sizes[index],
          "bold": bolds[index],
          "horizontalAlignment": this.alignConventer(horizontalAlignments[index]),
          "verticalAlignment": verticalAlignments[index]
        });
    }

    return result;
  }

  colorStripper(colorString) {
    if (colorString == undefined || colorString == null) {
      return null
    }

    if (colorString.length == 9) {
      return "#"+colorString.substring(3, 9);
    } else {
      return colorString;
    }
  }

  alignConventer(horizontalAlignment) {
    if (horizontalAlignment == undefined or horizontalAlignment == null) {
      return null
    }

    return horizontalAlignment.replace('general-', '')
  }

  getFieldRow(sheet, name) {
    const firstColumnRowsRange = sheet.getRange(1, 1, sheet.getLastRow(), 1); // Get the range of the first column (field)
    const firstColumnRows = firstColumnRowsRange.getValues(); // Get the values of the range
    const foundIndex = firstColumnRows.findIndex((firstColumnRow) => firstColumnRow[0] == name);

    if (foundIndex < 0) {
      return null;
    } else {
      return foundIndex + 1;
    }
  }
}

Main.gs Add Web Request Handle:

const targetGoogleSheetID = "1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE";
// https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit#gid=275710641

const gaPropertyId = "318495208";

function debug() {
  cronjob();
}

function cronjob() {
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);

  const gaData = new GAData(yesterday);
  const inHouseReportData = fetchInHouseReportData(yesterday);
  
  const report = new DailyReport(targetGoogleSheetID, yesterday, gaData, inHouseReportData);
  report.execute();
}

function doGet(e) {
  return HtmlService.createTemplateFromFile('Web_DailyReport_ Scaffolding').evaluate();
}

function getDailyReportBody() {
  const html = HtmlService.createTemplateFromFile('Web_DailyReport_Body').evaluate().getContent();
  return html;
}

// FOR POST
// function doPost(e) {
//  ref: https://developers.google.com/apps-script/guides/web?hl=zh-tw
// }


// Simulate some data that might be obtained by hitting other platform APIs.
function fetchInHouseReportData(date) {
  // EXAMPLE REQUEST:
  // var options = {
  //   'method' : 'get',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   }
  // };
  // OR
  // var options = {
  //   'method' : 'post',
  //   'headers': {
  //       'Authorization':  'Bearer XXX'
  //   },
  //   'payload' : data
  // };

  // var res = UrlFetchApp.fetch(url, options);
  // const result = JSON.parse(res.getContentText());

  // REMEMBER, DUE TO SECURITY REASON, We can't customize user-agent.
  
  return {"registers": Math.floor(Math.random() * (180 - 30 + 1)) + 30} // MOCK DATA random 30~180
}

Add Web_DailyReport_ Scaffolding.html Web Dashboard framework, since our war room screen needs to automatically update content, we create a Web skeleton that periodically fetches HTML content using Ajax:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      function onSuccess(html) {
        if (html != null) {
          var div = document.getElementById('result');
          div.innerHTML = html;
        }
     }
     setInterval(()=>{
       google.script.run.withSuccessHandler(onSuccess).getDailyReportBody()
     }, 1000 * 60 * 60 * 1);
     google.script.run.withSuccessHandler(onSuccess).getDailyReportBody();
    </script>
  </head>
  <body>
    <div id="result">Loading...</div>
  </body>
</html>

New Web_DailyReport_Body.html where the actual data is rendered into HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
    table {
        border-collapse: collapse;
        width: 100%;
        text-align: center;
    }
    th, td {
        border: 1px solid #000000;
        padding: 8px;
        text-align: center;
        font-size: 36px;
    }
      </style>
  </head>
  <body>
      <h1 style="text-align:center">ZHGCHG.LI</h1>
      <table id="dataTable">
        <tbody>
          <?
          // Display data from the past 7 days
          const dashboard = new WebDailyReport(targetGoogleSheetID, 7);
          // Starting from yesterday
          const yesterday = new Date();
          yesterday.setDate(yesterday.getDate() - 1);
          const data = dashboard.allData(yesterday);
          for(const rowIndex in data) {
            const row = data[rowIndex];
            ?>
            <tr>
              <?
              for(const columnIndex in row) {
                const column = row[columnIndex];
                ?>
                <td style="background-color: <?=column["backgroundColor"]?>; color: <?=column["color"]?>; text-align: <?=column["horizontalAlignment"]?>;">
                  <?=column["value"]?>
                </td>
                <?
              }
              ?>
            </tr>
            <?
          }
          ?>
        </tbody>
      </table>
      <script>
  </body>
</html>

Please note, we are fetching data from yesterday onwards for the past 7 days for comparison, today’s data will not be displayed.

The project directory after completing the above steps is as follows:

Test Deployment:

Click on the top right corner of the project “Deploy” -> “Test Deployment”

• After deployment, click the URL to view the test results.
• Please note this URL is for one-time testing only. If the code is adjusted, you need to click the test deployment operation again.

If stuck on Loading… or a server error occurs, you can go back to the “Executions” tab in the editor to check the error message:

Complete Final Deployment:

If the test is fine, you can complete the final deployment and release the URL.

Click on the top right corner of the project “Deploy” -> “New Deployment” -> Top left corner “Select type” -> “Web app”:

• Execution Identity: Default is the current account (same as Google Apps Script user)
• Who can access: Set to anyone with the link can access, or restrict to organization only, requiring Google login to access.
• Deployment completed, get the URL.

Code changes require redeployment to take effect:

Please note that when the code changes, you need to redeploy (the URL will not change) for the changes to take effect, otherwise, it will always be the old version.

Click on the top right corner of the project “Deploy” -> “Manage deployments”:

Click on the top right corner “Pen 🖊️ ICON” -> “Version” -> “Create new version” -> “Deploy”.

After deployment, click the URL, or go back to the original URL and refresh to see the new changes.

🎉🎉Completed! All our RPA requirements are now fulfilled.🎉🎉

Final result:

(Modify the program to backfill this month’s data, otherwise, there will only be one entry for yesterday in the new data)

https://script.google.com/macros/s/AKfycbz2Vk-ikU8DSXjpnLq9r6HNAn3zlNAosvDoItG0cxy0bmItRDSVyEzTdwsL2HyFUz99/exec

Complete Google Sheet Demo:

• Google Sheet: https://docs.google.com/spreadsheets/d/1-9lZCpsu3E7eDmO-lMkXJXQ6Y6KK4SiyU6uBODcDcFE/edit?usp=sharing
• Web GUI URL: https://script.google.com/macros/s/AKfycbz2Vk-ikU8DSXjpnLq9r6HNAn3zlNAosvDoItG0cxy0bmItRDSVyEzTdwsL2HyFUz99/exec
• Google Apps Script: https://script.google.com/home/projects/1vHgXPTV_q8MC75FVzAEtzD2JPVnPBpMfFXbjZR7SLMVjoEH1FcjKlo8l/edit

Finally, here are some other daily life applications:

Robotic Process Automation with Google Apps Script — Github Repo Star Notifier to Line

Robotic Process Automation with Google Apps Script — Notion Database to Calendar

Previously implemented the Notion to Calendar functionality.

The implementation method is to connect to the Notion API to fetch Database data and apply it to generate an ICS format webpage, which is then deployed as a public webpage; this URL can be added to Apple Calendar.

Main.gs :

// Constant variables
const notionToken = "XXXXX";
const safeToken = "XXXXX";

function doGet(e) {
  const ics = HtmlService.createTemplateFromFile('ics');

  if (e.parameter.token != safeToken) {
    return ContentService.createTextOutput("Access Denied!");
  }

  ics.events = getQuickNote();
  
  return ContentService.createTextOutput(ics.evaluate().getContent()).setMimeType(ContentService.MimeType.ICAL);
}

function debug() {
  const ics = HtmlService.createTemplateFromFile('ics');
  ics.events = getQuickNote();
  Logger.log(ics.evaluate().getContent());
}

function getQuickNote() {
  // YOUR FILTER Condition:
  const payload = {
    "filter": {
      "and": [
        {
          "property": "Date",
            "date": {
            "is_not_empty": true
          }
        }
        ,
        {
          "property": "Name",
            "title": {
            "is_not_empty": true
          }
        }
      ]
    }
  };
  const result = getDatabase(YOUR_DATABASE_ID, payload);
  var events = [];
  for (const index in result.results) {
    const item = result.results[index]
    const properties = item.properties;

    const id = item['id'];
    const create = toICSDate(item["created_time"]);
    const edit = toICSDate(item["last_edited_time"]);
    const startDate = properties['Date']['date']['start'];
    const start = toICSDate(startDate);
    var endDate = properties['Date']?.['date']?.['end'];
    if (endDate == null) {
      endDate = startDate;
    }
    const end = toICSDate(endDate);
    const type = properties['Type']?.['multi_select']?.[0]?.['name'];

    const title = "["+type+"] "+properties?.['Name']?.['title']?.[0]?.['plain_text'];
    const description = item['url'];
    
    events.push(
      {
        "id":id,
        "create":create,
        "edit":edit,
        "start":start,
        "end":end,
        "title":title,
        "description":description
      }
    )
  }
  return events;
}
// TO UTC Date
function toICSDate(date) {
  const icsDate = new Date(date);
  icsDate.setHours(icsDate.getHours() - 8);
  return Utilities.formatDate(icsDate, "GMT+8", "yyyyMMdd'T'HHmmss'Z'");// 20240304T132300Z
}

// Notion
function getDatabase(id, payload) {
  const url = 'https://api.notion.com/v1/databases/'+id+'/query/';
  const options = {
    method: 'post',
    headers: {
      'Authorization': 'Bearer '+notionToken,
      'Content-Type': 'application/json',
      'Notion-Version': '2022-06-28'
    },
    payload: JSON.stringify(payload)
  }; 
  const result = UrlFetchApp.fetch(url, options);
  return JSON.parse(result.getContentText());
}

ics.html :

BEGIN:VCALENDAR
PRODID:-//Google Inc//Google Calendar 70.9054//EN
VERSION:2.0
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:NotionCalendar
X-WR-TIMEZONE:Asia/Taipei
BEGIN:VTIMEZONE
TZID:Asia/Taipei
X-LIC-LOCATION:Asia/Taipei
BEGIN:STANDARD
TZOFFSETFROM:+0800
TZOFFSETTO:+0800
TZNAME:CST
DTSTART:19700101T000000
END:STANDARD
END:VTIMEZONE
<?
  for(const eventIndex in events) {
    const event = events[eventIndex];
    ?>
BEGIN:VEVENT
DTSTART:<?=event["start"]?>

DTEND:<?=event["end"]?>

DTSTAMP:<?=event["edit"]?>

UID:<?=event["id"]?>

CREATED:<?=event["create"]?>

LAST-MODIFIED:<?=event["edit"]?>

SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:<?=event["title"]?>

DESCRIPTION:<?=event["description"]?>

TRANSP:OPAQUE
END:VEVENT
<?
  }
?>
END:VCALENDAR

As mentioned earlier, deploy as a web service, click on the top right corner of the project “Deploy” -> “New Deployment” -> top left corner “Select Type” -> “Web Application”:

• Who can access should be set to everyone, as Google login verification cannot be performed when adding Calendar.

Add the URL to the calendar subscription, and it’s done 🎉🎉🎉🎉 !

Commercial Time

If you and your team have automation tool or process integration needs, whether it’s Slack App development, Notion, Asana, Google Sheet, Google Form, GA data, various integration needs, feel free to contact me for development.

If you have any questions or comments, feel free to contact me.

===

本文中文版本

===

This article was first published in Traditional Chinese on Medium ➡️ View Here