Schedule Looker Reports to G-Chat Space using Google Apps Script.

[Mawuli]

In our busy lives, finding ways to simplify and automate tasks is crucial for productivity. Thankfully, Google Apps Script provides a powerful solution for automating tasks within Google Workspace, such as Gmail and Google Drive. In this article, we will explore how to use Google Apps Script to create a webhook function that sends notifications and updates to a designated chat space. 

Before we dive into the technical details, let’s make sure you have everything you need to follow along:

Prerequisites:

• A basic understanding of JavaScript.
• Access to a Google Workspace account.

Setting up the webhook function: The webhook function is the heart of our automation process. It sends notifications to a specific chat space using the Google Chat API. Let’s break down the code and see how it work.

To make things easier, we’ll guide you through each step. First, we need to define the URL for the Google Chat API. This URL acts as the endpoint where our notifications will be sent.

To find your webhook URL in Google Workspace:

1. Go to the Google Chat application in your Google Workspace account.
2. Create or select a chat space where you want to receive the notifications.
3. Click on “Apps & integrations” drop from the drop down.

4. Proceed to click on manage webhooks, enter a name for webhook (for eg: “Looker Gchat Bot”), click on save. In the next dialog box click on the “copy” icon to copy the webhook url.

In the “Webhook URL” section, you will find the URL needed for the url variable in the webhook function. Copy this URL and replace the placeholder.

function webhook() {
  // Define the URL for the Google Chat API
  const url = "https://chat.googleapis.com/v1/spaces/AAAAdoadlss/messages?key=YOUR_API_KEY&token=YOUR_TOKEN";

Next, we configure the options for the HTTP request. We set the method to “post” since we want to send data to the API. Additionally, we specify the content type as JSON to ensure proper formatting of our payload. Speaking of which, the payload contains the actual notification content. Feel free to customize the title, image URL, and message text to suit your needs.

// Configure the HTTP request options
  const options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json; charset=UTF-8"
    },
    "payload": JSON.stringify({
      "cards": [{
        "header": {
          // Insert the title of your message
          "title": "My Website",
          // Insert the url for your app icon
          "imageUrl": "https://myimage.com/appicon.jpg",
          "imageStyle": "IMAGE"
        },
        "sections": [{
          "widgets": [{
            "textParagraph": {
            // Insert the text details or notes
              "text": "My Website Update: " + new Date().toDateString(),
            }
          },
          {
            "buttons": [{
              "textButton": {
              // Insert the CTA for the button
                "text": "OPEN LINK",
                "onClick": {
                  "openLink": {
                    // Insert the destination url
                    "url": "https://yourdestinationurl.com"
                  }
                }
              }
            }]
          }]
        }]
      }]
    })
  };

Finally, we use the next section to send the HTTP request to the Google Chat API. The response from the API is logged using Logger.log() for troubleshooting purposes.

// Send the HTTP POST request to the Google Chat API
  const response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}

Configuring the trigger: To automate the execution of the webhook() function, we need to set up a trigger within Google Apps Script. Here's how you can do it:

1. Open the Google Apps Script project that contains your code.
2. In the toolbar, click on “Edit” and select “Current project’s triggers.”
3. Click on the “+ Add Trigger” button in the bottom right corner.
4. In the dialog that appears, configure the trigger settings as follows:

• Choose the webhook function from the "Run" dropdown menu.
• Select the event source as “Time-driven.”
• Choose the desired interval (e.g., every minute, every hour, etc.) for the trigger.
• Click on “Save” to create the trigger.

By setting up the trigger, the webhook() function will be automatically executed based on the specified interval.

Conclusion: By combining the power of Google Apps Script and the Google Chat API, we can automate notifications and streamline our workflow. With the webhook function, you can send customized notifications to a chat space. By automating these tasks and configuring triggers, you’ll save time and improve efficiency in your daily work. So why not give it a try and simplify your workflow today?