Automate Your Monitoring with Google Apps Script: Sending Google Sheets Data to Webhooks
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 forIncoming 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 asTime-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!