Implementing Daily Data Report RPA Automation with Google Apps Script

---

ℹ️ℹ️ℹ️ The following content is translated by OpenAI.

Click here to view the original Chinese version. | 點此查看本文中文版

---

Implementing Daily Data Report RPA Automation with Google Apps Script

Robotic Process Automation (RPA) for Google Workspace services using Google Apps Script

Photo by Possessed Photography

Robotic Process Automation

RPA (Robotic Process Automation) translates to “Process Automation Robot” in Chinese. Looking back at human history, from manual gathering to the Stone Age, then to agricultural civilization, and from the industrial revolution of the last century to the rise of information technology in the past 20 years, human work efficiency and productivity have seen exponential growth. Throughout this journey, RPA applications have been ubiquitous: the waterwheel in the agricultural era (automating the threshing process), the textile machines of the industrial revolution (automating textile work), factory robotic arms (automating assembly tasks), and finally, the automation of information-related tasks, such as automatic report queries and notifications, which this article will introduce.

I must admit, I only recently learned this term. Since my first job seven years ago, I have been doing RPA-related tasks, such as writing web scrapers for data statistics, automating CI/CD processes, automating data queries, automating stability data alerts, and automating daily routine operations. However, I previously referred 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 individual problems,” lacking a comprehensive evaluation and analysis in the early stages, the use of No/Low Code tools, regulatory considerations, operational monitoring, actual data statistics, continuous improvement, and promoting corporate culture. All of these are essential for a complete RPA implementation. However, as mentioned earlier, I only recently became aware of this professional field, so let me start by sharing a practical article!

There are many platforms that provide RPA services, such as Automation Anywhere, UiPath, Microsoft Power Automate, Blue Prism, or Zapier, IFTTT, Automate.io… You should choose the appropriate service based on the specific problem you want to solve.

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

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

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

Based on the concept of transforming active to passive, we can implement an RPA that automatically notifies us when there are new changes, instead of manually checking for new notifications.

For instance: the previously implemented Gmail to Slack that forwards specific notification emails to work groups.

Benefits Assessment of Robotic Process Automation

Previously, in the “2021 Pinkoi Tech Career Talk — Secrets of High-Efficiency Engineering Teams,” I shared the cost issues of incremental gains and flow interruptions. Suppose a repetitive task takes 15 minutes each time and occurs 10 times a week; over a year, that amounts to nearly 130 hours wasted. If we also consider the cost of “switching flow,” we might end up wasting nearly 200 hours a year.

2021 Pinkoi Tech Career Talk — Secrets of High-Efficiency Engineering Teams

The term “flow switching” refers to the time spent when we are focused on important tasks and need to pause to handle other matters, then return to regain our focus.

The benefits assessment for developing RPA can refer to the diagram below; as long as the time required for development and the frequency of occurrence exceed the wasted time, it is worth investing resources to implement:

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

• X-axis: Task occurrence frequency, e.g., 50/Day (50 times a day)
• Y-axis: The amount of human time required to complete each task
• The time cost range is calculated over the past 5 years, with the middle of the table indicating the wasted time and labor costs over 5 years
• White indicates that the potential time cost for automation is less than the benefits gained, making it not worth improving
• Green indicates tasks worth automating
• Red strongly suggests transitioning to automation

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

The Relationship Between Robotic Process Automation and AI

With the rise of AI, RPA is often mentioned; however, I believe there is no direct relationship between RPA and AI. RPA existed long before the era of AI, and the benefits of implementing AI may not surpass those of a well-established 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, while RPA could only perform precise, routine tasks in the past, with AI, it can handle more ambiguous, dynamic, and intelligent decision-making tasks.

Robotic Process Automation at Google Workspace

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

However, Google does not provide direct RPA services, but we can achieve this through the following services:

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

I have not worked with the No Code platform App Sheet, but I have considerable experience with Cloud Functions and Google Apps Script. Below are some personal experiences and insights from my previous usage:

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 a complete authentication mechanism
• Maximum execution time limit: 60 minutes
• Pay for what you use: charges based on the number of executions, execution time, different processors, and memory used
• Subject to cold start issues (if not called for a long time, the first call will require a longer response time)
• Cannot directly connect to Google services; requires Auth/API authentication
• Free tier details: Cloud Functions offers a permanent free tier for computing time resources, including GB/second and GHz/second allocations. In addition to 2 million calls, this free tier also provides 400,000 GB/seconds and 200,000 GHz/seconds of computing time, as well as 5 GB of internet data transfer per month. The usage quota for the free tier is calculated based on the equivalent dollar amount of the above level 1 pricing. Regardless of whether the execution function’s region uses level 1 or level 2 pricing, the system will allocate an equivalent dollar amount to you. However, when deducting the free tier quota, the system will follow the function execution region’s level (level 1 or level 2). Please note that even if you are using the free tier, you must have a valid billing account.

In summary, Cloud Functions is recommended for more complete and complex RPA integration functions or when there are more external API integration needs.

Previous use cases for Cloud Functions include:

• [Slack ChatGPT Conversation Bot]: Slack & ChatGPT Integration
• [Automated Check-in Bot]: Using Python + Google Cloud Platform + Line Bot to Automate Routine Tasks

These are used when integrating with non-Google Workspace services and bridging other external services.

Google Apps Script

• Convenient, simple, and quick
• Completely free
• No complicated 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
• Uses Google network to execute web requests
• Can only be developed using Google Apps Script (based on JavaScript)
• Does not support package management tools, no version control features
• Due to security issues, customizing Request User-Agent information is not allowed
• Execution time limit: scripts must complete work within 6 minutes, or they will be terminated.
• For other limitations and quotas, please refer to the official GAS information:

Previous use cases for Google Apps Script include:

• [Integrating Slack x Google Form x Google Sheet]: Creating a Fully Automated WFH Employee Health Status Reporting System
• [Integrating Slack x Gmail]: Using Google Apps Script to Forward Gmail Emails to Slack
• [Integrating Google Analytics x Slack]: Crashlytics + Google Analytics Automatic Query for App Crash-Free Users Rate
• [Integrating Firebase Crashlytics x Big Query x Slack]: Crashlytics + Big Query for a More Immediate and Convenient Crash Tracking Tool
• [Integrating Github x LineBot]: Using Google Apps Script to Create a Free Github Repo Star Notifier in Three Steps

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 prefer to use Google Apps Script (because using Cloud Functions requires implementing the complete Google service authentication process).

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

Finally, we arrive at the main topic of this article: using Google Apps Script to implement RPA automation for Google services.

Background

The product team needs to query Google Analytics data daily and fill it into a Google Sheet data report for team analysis of trends. They also need to publish the daily data content on a dashboard screen to keep all members informed of the current status.

Colleagues spend about 30 minutes each day upon arriving at the office to complete this task; if there are other matters to handle, they must wait until this routine work is finished before starting or may delay the publication of daily data messages.

A simple estimate of RPA benefits:

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

Therefore, we only need to invest one week of development time to solve the work consumption of the colleague responsible for data checking in the long term, 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 to build 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 dummy data for demo purposes; starting from 2024/04/13, it will be particularly low or remain at 0, as my zhgchg.li GA truly has “0” traffic Q_Q.

Tasks to Complete

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

Declaration

Due to the explanatory nature of the article, the following code will be kept as simple as possible to enhance clarity. You can modify it according to your actual needs.

At the end of the article, I have attached the complete public Google Sheet & Google Apps Script. If you prefer not to go step by step, you can directly modify the template attached at the end of the article.

Step 1. Create Google Apps Script

Simply select “Extensions” -> “Apps Script” on the report you want to automate, and it will automatically create a Google Apps Script linked to the Google Sheet report.

Additionally, you can create a [Google Apps Script](https://www.google.com/script/start/){:target="_blank"} directly from the Google Apps Script homepage, but this will not link it to Google Sheets.

> **You do not need a link to operate with the corresponding Google Sheet.** Both methods of creation are valid; the difference lies in the ownership of the script. If it is linked to a report, it belongs to the report owner. If created by yourself, it belongs to the creator. Ownership is important because if that account is deactivated due to leaving the company, the script may become invalid or be deleted.

![](/assets/f6713ba3fee3/1*vnCPiALLTi4CIcYbASQpOg.png)

After creating the script, we can rename our script project name from the top.
#### Basic Knowledge of Google Apps Script

Before moving on to writing the program, let's cover some basic knowledge of Google Apps Script.

**About the Editor**

By default, the SDKs for Google services are included (no special import is needed to call them):
- CalendarApp Calendar
- DocumentApp Google Drive
- FormApp Google Form
- SpreadsheetApp Google Sheet
- GmailApp Gmail
- [Others…](https://developers.google.com/apps-script/reference/slides/border?hl=zh-tw#getdashstyle){:target="_blank"}

![](/assets/f6713ba3fee3/1*PKO-daNhyquINvk-_LfgQQ.png)

1. **File:**
   You can add multiple `.gs` files to store different object codes for better organization. All files will run under the same Namespace and lifecycle, so be careful with object names and variable names, as duplicates may overwrite each other. In addition to `.gs` script files, you can also add `.html` HTML Template files for rendering Web UI. (More on this later)
   
2. **Library:**
   You can use libraries written by others (a.k.a. Lib) by importing them through their Script ID. Of course, you can also deploy your own scripts as libraries for others to use. Additionally, there are some tools packaged by experts that can be used, but the downside is that you can only search for Script IDs through Google, as there is no official database list for querying.
   e.g. HTML Parser Tool Cheer.io Script ID: `1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0`
   
3. **Services:**
   The SDKs for Google services that are not included by default can be added here for use.
   e.g. Google Analytics Data
   
4. **Undo, Next Action**
5. **Save** or Control + s
6. **Run** or Control + r
   If an error occurs, it will directly prompt in the Console and terminate the script.
   
7. **Debugging**
   Execution will pause at the Break point (10) and open the Debug View on the right side; you can press continue to execute.
   If an error occurs, it will pause and open the Debug View on the right side.
   
8. **Debugging, Target Method (Function Name)**
   You can only select methods owned by the currently selected file.
   
9. **View Execution Logs in the Editor**

> _Another point to note is formatting; in some browsers, pressing "Control + [" for indentation may trigger a return to the previous page, so be cautious!_

[**Google Apps Script GitHub Assistant Chrome Extension Version Control Plugin**](https://chromewebstore.google.com/detail/lfjcgcmkmjjlieihflfhjopckgpelofo){:target="_blank"}
- It is recommended to install this Extension to connect Google Apps Script with git, enabling version control functionality to prevent accidental changes.

![](/assets/f6713ba3fee3/1*NIFgOdZ2EIv-FCfYOxCmsA.png)

![](/assets/f6713ba3fee3/1*JQyQCdn-FAtItaJ2qrLnng.png)

![](/assets/f6713ba3fee3/1*TqsqHeccSt8-qN4Tav0kwg.png)

- If you encounter Push/Pull Errors or if it does not respond when clicked, please follow the steps above: "Options" -> Connect to Github or re-verify Google authorization.

**Logger Message**

You can use the following Script in conjunction with Debug to print Debug Logs in the Console below.
```javascript
Logger.log("Hi")

Execution Logs, Error Information

Logs or errors during execution in the editor will be displayed directly. If you want to check execution logs or errors that occurred during automatic execution, you can view them in the “Execution Items” tab.

Automatic Triggers

In the “Trigger Conditions” tab, you can set how the methods in the script should be automatically triggered. The automatic trigger conditions you can set include:

• When Google Sheet: Opens, Edits, Content Changes, Submits Form
• Scheduled triggers: Execute once every X minutes, X hours, X days, X weeks, X months
• Specific date triggers: Execute at YYYY-MM-DD HH:MM
• When Calendar: Updates

Error notification settings allow you to configure how you will be notified when the script encounters an error.

Grant Execution Permissions

The first time you execute/deploy or add imported services and resources, you need to re-run the identity authorization. After authorization, all subsequent executions will use the identity you authorized, so ensure that the account you authorize (usually the current one) has access to the resource or service. (e.g. you need to have access to that Google Sheet).

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

If you see “Google hasn’t verified this app,” it is because the app we are developing is for personal use and does not need to go through Google verification.

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

Once authorization is complete, you can successfully execute the script. If there are no changes to the resources, you do not need to re-authorize.

2. Obtain/Create the Corresponding Date Sheet

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

We create the following multiple files to conveniently 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 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("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("Registration Count", 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 using the given row number (the result of `COLUMN()`) and a 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 of 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 letter of the column, e.g., "C".
    // 4. INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 4) here & 4 should actually be &4. The result of `SUBSTITUTE` will concatenate with the number 4, forming a string like "C4", and then the INDIRECT function will convert this string into the corresponding cell reference. So, if you use this formula in any cell of column C, it will reference C4.
    // 5. Similarly, `INDIRECT(SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & 5)` will reference the cell in the fifth row of the same column. For example, if you use this formula in any cell of column C, it will reference C5.
    // 6. Finally, the values of the two INDIRECT function-referenced cells are added together.
  }

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

  }

  // Get the target Sheet for the given date
  getSheet() {
    // Distinguish Sheets by month, find the Sheet for the current month
    var thisMonthSheet = this.separateSheet.getSheetByName(this.getSheetName());
    if (thisMonthSheet == null) {
      // If not, 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 and 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, set the column name, Pinned, width 200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // Fill in the column 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 two rows (Date, Week)
    thisMonthSheet.setFrozenRows(2);

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

    // Fill in the total 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) {
        // 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 determine if there is an error in the formula and returns 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 encounters an error. In this context, if there is an error in the calculation of the SUM function, it returns 0.
        // 2. The SUM(range) function is used to calculate the total 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 "1" in the address returned by the ADDRESS function with something else.
        // 5. The ADDRESS(row, column, [abs_num], [a1], [sheet]) function returns the corresponding cell address based on the given row and column numbers. Here, ADDRESS(1, 1, 4) generates the cell address for the first row and first column, but since abs_num is 4, the address does not include the sheet 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 current row     
      }
    }

    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:

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 completing, 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 the default fields and total column. If it already exists, there will be no response.

3. Connect to Google Analytics to Retrieve 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 build it while comparing with the filtering conditions in the GA 4 backend.
• This article uses querying the number of Sessions as an example, distinguishing device Grouping.

After logging in and authorizing, select the target resource:

Note the number displayed under the property, which is your GA Property ID for querying.

Set the query parameters and Filter conditions:

```markdown
Press "Make Request" to obtain the Response result:

![](/assets/f6713ba3fee3/1*UArMZH6b0LPKj8_HTFrX9g.png)

You can synchronously compare the data from the GA 4 backend under the same conditions to see if they match. If there is a significant discrepancy, it may be due to a missing filter condition, so please check again.

#### Note

Here’s a small pitfall discovered by a marketing colleague: some data in GA may have a delay issue, meaning that the numbers you check today might differ from those checked yesterday (for example, the bounce rate). Therefore, it’s best to backtrack the data a few days to ensure that the final numbers are accurate.

> _Once the GA Debug Tool shows no issues, we can convert it to Google Apps Script._

Add a new `GAData.gs` file:
```javascript
// 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");
  }
}

Add test content to Main.gs:

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 obtain the program’s fetching results:

OK! The comparison matches.

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

4. Fill in Data

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

Adjust DailyReport.gs to add columns by date and the logic for filling in data:

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("Registration Count", 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 column

    // Fill in the column 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, create a new monthly Sheet
      thisMonthSheet = this.makeMonthSheet();
    }

    return thisMonthSheet;
  }

  // Naming the monthly Sheet
  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, column name, set Pinned, width 200
    thisMonthSheet.insertColumnsBefore(1, 1);
    thisMonthSheet.setFrozenColumns(1);
    thisMonthSheet.setColumnWidths(1, 1, 200);

    // Fill in the column 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 two rows (Date, Day)
    thisMonthSheet.setFrozenRows(2);

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

    // Fill in the total 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...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 column
  // Add a column from the most recent day
  makeOrGetDateColumn(sheet) {
    const firstRowColumnsRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // Get the first row (date) data range
    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 corresponding date column Index

    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 column
        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 column Format style
  setFormat(range, format) {
    if (format != null) {
      range.setNumberFormat(format);
    }
  }

  // Fill content into the column
  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 connection 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();
  
}

// Simulated partial data that may be obtained from 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, we return to Main.gs, select “debug,” and press debug to check if the execution results are correct and if there are any errors.

Back to Google Sheet! Success! We have successfully auto-added the data for that date.

5. Set Up Scheduling for Daily Automatic Execution

Once the script is complete, you just need to set the automatic trigger conditions to run it daily.

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 case that today's data checks yesterday's data for complete information
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();
}

// Simulated partial data that may be obtained from 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
}

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

• Choose the function you want to execute: the newly added Main.gs function cronjob
• Choose the deployment to execute: the top (Head, meaning the latest version)
• Select the event source: time-driven
• Select the time-based trigger type: daily timer
• Select the time period: AM 4:00 — AM 5:00 (GMT+08:00) Usually, it will execute right at 4:00 AM.
• Error notification settings: whether to notify immediately when the script encounters an error or to summarize once daily.

Save the settings, and you’re done.

You can later check the execution log results in the “Execution Items” tab:

Thus, we have completed the automation of querying, adding data, and filling in data for the report RPA function. 🎉🎉🎉

Build a Web GUI Dashboard

Next, there is a secondary requirement: we need to create a simple web display of daily data (similar to a situation room concept) that will be directly shown on a large screen on the team’s back wall.

The effect is as shown in the image below:

Add Web_DailyReport.gs to write code that reads Google Sheets and converts columns and styles into HTML format for presentation:

class WebDailyReport {
  constructor(sheetID, dayCount) {
    this.separateSheet = SpreadsheetApp.openById(sheetID);
    this.dayCount = dayCount;
    this.sheetRows = [
      "Date",
      "Day",
      "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 first row (date) data range
        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(preDate, "GMT+8", "yyyy/MM/dd"))); // Find the corresponding date column Index
        
        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 attribute values into 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 || horizontalAlignment == null) {
    return null
  }

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

getFieldRow(sheet, name) {
  const firstColumnRowsRange = sheet.getRange(1, 1, sheet.getLastRow(), 1); // Get the data range of the first column
  const firstColumnRows = firstColumnRowsRange.getValues(); // Get the values of the data 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
// }


// Simulated partial data that may be obtained from 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>

Add 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 for the last 7 days
          const dashboard = new WebDailyReport(targetGoogleSheetID, 7);
          // Start 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 that we are pulling data for the last 7 days starting from yesterday, and today’s data will not be displayed.

After completing the above steps, the project file directory is as follows:

Test Deployment:

Click on the “Deploy” button in the upper right corner of the project -> “Test Deployment Task”

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

If stuck on Loading… or a server error occurs, you can return to the editor’s “Execution Items” tab to check the error messages:

Complete Formal Deployment:

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

Click on the “Deploy” button in the upper right corner of the project -> “Add Deployment Task” -> “Select Type” in the upper left corner -> “Web Application”:

• Execution identity: Default is the current account (same as Google Apps Script user)
• Who can access: Set to anyone with the URL can access, or set to only organization can access, which will require Google login to access.
• After deployment, obtain 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 show the old version.

Click on the “Deploy” button in the upper right corner of the project -> “Manage Deployment Tasks”:

Click the pencil 🖊️ ICON in the upper right corner -> “Version” -> “Create New Version” -> “Deploy”.

After deployment is complete, click the URL, or return to the original URL and refresh to see the new changes.

🎉🎉 Done! All our RPA requirements have been completed. 🎉🎉

Final result:

(Modify the program to supplement this month’s data; otherwise, there will only be one record for yesterday under 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 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 a simple Notion to Calendar function.

The implementation method is that I connect to the Notion API to fetch Database data and apply it to produce an ICS format webpage, which is then deployed as a public webpage; adding this URL to Apple Calendar will suffice.

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 before, deploy as a web service, click on the “Deploy” button in the upper right corner -> “Add Deployment Task” -> “Select Type” in the upper left corner -> “Web Application”:

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

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

Business Time

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

If you have any questions or suggestions, please contact me.

```

---

This article was first published on Medium ➡️ Click Here

Automatically converted and synchronized using ZMediumToMarkdown and Medium-to-jekyll-starter.

Improve this page on Github.

Follow Me on Medium 999+ Followers

2,895 Total Views
Last Statistics Date: 2025-03-25 | 2,805 Views on Medium.