Automate Your Monitoring with Google Apps Script: Sending Google Sheets Data to Webhooks

6 min readJul 28, 2024

In our previous post, we integrated Jira with Google Sheets using the Jira Cloud for Sheets extension. Now, let’s automate your monitoring by sending your Google Sheets data to a webhook (Slack/Mattermost) using Google Apps Script. This will allow you to receive real-time updates and alerts based on your Jira data.

Step 1: Set Up Your Webhook

For Slack:

Create a New Webhook:

Navigate to your Slack workspace.
Go to Apps and search for Incoming Webhooks.
Click Add to Slack.
Select the channel where you want to receive updates and click Add Incoming Webhooks integration

2. Copy the Webhook URL:

After setting up, you will receive a webhook URL. Copy this URL for use in your script.

For Mattermost:

Create a New Webhook:

Navigate to your Mattermost workspace.
Go to Integrations > Incoming Webhooks.
Click Add Incoming Webhook.
Select the channel where you want to receive updates and provide a description.

2. Copy the Webhook URL:

After setting up, you will receive a webhook URL. Copy this URL for use in your script.

Step 2: Write the Google Apps Script

Open the Script Editor:

In your Google Sheet, go to Extensions > Apps Script.

2. Create the Script:

a) Set Up Webhook URL and Headers

var webhookUrl = 'https://yourwebhookurl.com/xxxyyyzzz111222333';
var headers = { 'Content-Type': 'application/json' };

Webhook URL: This is the URL where the data will be sent. It’s specific to the Mattermost channel.
Headers: Setting the content type to application/json indicates that the data being sent is in JSON format.

b) Get the Current Date

var currentDate = new Date();
var dateString = currentDate.toISOString().split('T')[0];

Current Date: Retrieves the current date and converts it to ISO string format.
Date String: Extracts just the date part (YYYY-MM-DD) from the ISO string.

c) Retrieve Data from “Task List” Sheet

var taskSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Task List");
var dataRange = taskSheet.getRange(2, 1, taskSheet.getLastRow() - 1, 8);
var data = dataRange.getValues();

Task Sheet: Accesses the “Task List” sheet.
Data Range: Defines the range of cells to read, starting from the second row and first column.
Data: Retrieves the values within the defined range as a 2D array.

d) Retrieve Data from “Assignee” Sheet and Create a Mapping

var mappingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Assignee");
var mappingRange = mappingSheet.getRange(2, 1, mappingSheet.getLastRow() - 1, 2);
var mappingData = mappingRange.getValues();

var assigneeMapping = {};
mappingData.forEach(function(row) {
  assigneeMapping[row[0]] = row[1];
});

Mapping Sheet: Accesses the “Assignee” sheet.

Mapping Range: Defines the range of cells to read, starting from the second row and first column.
Mapping Data: Retrieves the values within the defined range as a 2D array.
Assignee Mapping: Creates an object to map assignee names to their corresponding values for easier lookup.

e) Build the Main Table Rows

var tableRows = data.map(function(row) {
  var key = row[0];
  var summary = row[1];
  var assignee = row[2];
  var priority = row[4];
  var status = row[5];
  if (assigneeMapping[assignee]) {
    assignee = assigneeMapping[assignee];
  }
  var link = `https://yourproject.atlassian.net/browse/${key}`;
  return `| [${key}](${link}) | ${summary} | ${assignee} | ${status} | ${priority} |`;
}).join('\n');

if (!tableRows) {
  tableRows = '| No issues to display | N/A | N/A | N/A | N/A |';
}

Map Data to Table Rows: Iterates over the data array and constructs a string for each row formatted as a Markdown table row.
Assignee Mapping: Replaces the assignee with its mapped value if it exists.
Link: Creates a hyperlink to the Jira issue.
Default Table Row: Sets a default message if no issues are present.

f) Retrieve Data from “Pending Issue” Sheet

var freshSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pending Issue");
var lastRow = freshSheet.getLastRow();
var pendingData = [];
if (lastRow > 1) {
  var pendingRange = freshSheet.getRange(2, 1, lastRow - 1, 8);
  pendingData = pendingRange.getValues();
}

Pending Issue Sheet: Accesses the “Pending Issue” sheet.
Last Row: Gets the last row with data.
Pending Data Range: If there are more than one rows, defines the range of cells to read, starting from the second row and first column.
Pending Data: Retrieves the values within the defined range as a 2D array.

g) Build the Pending Issue Table Rows

var tableRows2 = pendingData.map(function(row) {
  var key = row[0];
  var summary = row[1];
  var assignee = row[2];
  var priority = row[4];
  if (assigneeMapping[assignee]) {
    assignee = assigneeMapping[assignee];
  }
  var link = `https://yourproject.atlassian.net/browse/${key}`;
  return `| [${key}](${link}) | ${summary} | @irkham | ${priority} |`;
}).join('\n');

if (!tableRows2) {
  tableRows2 = '| No issues to display | N/A | N/A | N/A |';
}

Map Pending Data to Table Rows: Iterates over the pending data array and constructs a string for each row formatted as a Markdown table row.
Assignee Mapping: Replaces the assignee with its mapped value if it exists.
Link: Creates a hyperlink to the Jira issue.
Default Table Row: Sets a default message if no pending issues are present.

h) Define the Payload

var payload = {
  "channel": "portal-software",
  "username": "helpdesk-bot",
  "icon_url": "https://mattermost.com/wp-content/uploads/2022/02/icon.png",
  "text": `#### [Helpdesk Daily Update ${dateString}]\n@here Please help to check this issue.\n\n| Key | Summary | Assignee | Status | Priority |\n|:---------|:--------------------------------------------------------------------------------------------|:-------------------|:-------------------|:-------------------|\n${tableRows}\n\n@irkham this list of fresh issue that you need to check.\n\n| Key | Summary | Assignee | Priority |\n|:---------|:--------------------------------------------------------------------------------------------|:-------------------|:-------------------|\n${tableRows2}\n\nps: if there is any misassignment, feel free to change it right away.`
};

Payload Structure: Defines the payload to be sent to the webhook. It includes the channel, username, icon URL, and text message with Markdown formatting.
Table Rows: Inserts the constructed table rows into the payload text.

i) Send the Payload to the Webhook

var payloadString = JSON.stringify(payload);

var options = {
  'method': 'post',
  'contentType': 'application/json',
  'payload': payloadString,
  'headers': headers
};
UrlFetchApp.fetch(webhookUrl, options);

Convert to JSON: Converts the payload object to a JSON string.
Request Options: Defines the options for the HTTP request, including method, content type, payload, and headers.
Send Request: Uses UrlFetchApp.fetch to send the payload to the specified webhook URL.

Step 3: Test the Script

Run the Script:

Click the Run button in the Apps Script editor.

Check on Execution Log

Authorize the script to access your Google Sheets and external services.

Check the Webhook:

Verify that the data has been sent to your Slack/Mattermost channel.

Step 4: Automate the Script

Create a Trigger:

In the Apps Script editor, go to Triggers (clock icon).
Click + Add Trigger.

Choose the sendToWebhook function, select the event source as Time-driven, and set your desired frequency (e.g., daily, hourly).

Save the Trigger:

Save the trigger to automate the script execution.

Conclusion

By following these steps, you can automate the process of sending Jira data from Google Sheets to your Slack/Mattermost channel using Google Apps Script. This setup will provide real-time monitoring and updates, making your workflow more efficient and responsive.

Stay tuned for more tutorials on optimizing your project management and reporting tools!