Automated Notification for Excel leverages Google Apps Script to streamline communication by automatically sending notifications upon specific changes in your Excel sheet. This solution allows you to configure notifications via email and Google Chat groups, ensuring stakeholders stay informed in real-time.
- Trigger-based: Notifications are sent when a designated cell is edited, ensuring timely updates.
- Customizable: Define email addresses, Google Chat group IDs, and message content to match your needs.
- Flexible: Adapt the code to different cell addresses, triggers, and notification methods.
- Extensible: Explore other integration possibilities like SMS, Slack, or custom webhooks.
- A Google Account
- A Google Sheet
-
Create a Google Sheet: Design your sheet with the data you want to monitor and a dedicated "Notification Trigger" cell (e.g., A1). Excel Notifications sheet
-
Add Apps Script:
- Go to Tools > Script editor.
- Paste the provided code, replacing placeholders with your values.
- Save the project (e.g., "Excel Notifications").
-
Authorize the Script:
- Run the script and grant necessary permissions when prompted.
-
Set Trigger (Optional):
- Go to Resources > Current project's triggers.
- Create a time-driven trigger to run the script periodically, catching notifications even without direct edits.
// Placeholders are indicated with comments (// Replace with ...)
// Update placeholders with actual values before running the script
// Use code with caution.
// Function to run on every edit in the sheet
function onEdit(e) {
// Replace with actual sheet name and cell address
const sheetName = "Notification Sheet";
const cellAddress = "A1";
// Check if edited sheet and cell match expectations
if (e.source.getActiveSheet().getName() !== sheetName || e.range.getAddress() !== cellAddress) {
return;
}
// Retrieve data from the sheet
// Replace cell addresses with actual ones
const userStoryDetails = sheetName.getRange("B1").getValue();
const teamName = sheetName.getRange("C1").getValue();
const envName = sheetName.getRange("D1").getValue();
const formattedDateTime = sheetName.getRange("E1").getValue(); // Example: 2024-02-21 17:31
// Placeholders for customization
const emailAddresses = ["[email protected]", "[email protected]"];
const googleChatGroupId = "AAAABV0jd7w"; // Replace with your group ID
const subject = "Notification - Story ${userStoryDetails}";
// Compose message content
const messageParts = {
story: userStoryDetails,
team: teamName,
environment: envName,
deployedAt: formattedDateTime
};
const formattedMessage = `
*Story:* ${messageParts.story}
*Team:* ${messageParts.team}
Deployed to *${messageParts.environment}* at *${messageParts.deployedAt}*.
`;
// Send email notification (example using GmailApp)
try {
GmailApp.sendEmail(emailAddresses, subject, formattedMessage);
console.log("Email notification sent successfully.");
} catch (error) {
console.error("Error sending email:", error);
}
// Send Google Chat notification using UrlFetchApp
const payload = {
text: formattedMessage
};
const options = {
"method" : "post",
"payload" : JSON.stringify(payload),
"headers": {
"Content-Type": "application/json"
}
};
try {
UrlFetchApp.fetch(`https://chat.googleapis.com/v1/spaces/${googleChatGroupId}/messages`, options);
console.log("Google Chat notification sent successfully.");
} catch (error) {
console.error("Error sending Google Chat message:", error);
}
}in below curl replace URI after importing curl(use URI of google chat group added webhook)
to add webhook refer it here
final notification format with provided JS code
curl --location 'https://chat.googleapis.com/v1/spaces/xxxxxxxxxx/messages?key=xxxxxxxxxx-xxxxxxxxxxxxxxxxxxx&token=xxxxxxxxxxxxxxxxx-xxxxxxxxx' \
--header 'Content-Type: application/json' \
--data-raw '{
"cardsV2": [
{
"cardId": "unique-card-id",
"card": {
"header": {
"title": "Sasha",
"subtitle": "Software Engineer",
"imageUrl": "https://developers.google.com/chat/images/quickstart-app-avatar.png",
"imageType": "CIRCLE",
"imageAltText": "Avatar for Sasha"
},
"sections": [
{
"header": "Contact Info",
"collapsible": true,
"uncollapsibleWidgetsCount": 1,
"widgets": [
{
"decoratedText": {
"startIcon": {
"knownIcon": "EMAIL"
},
"text": "[email protected]"
}
},
{
"decoratedText": {
"startIcon": {
"knownIcon": "PERSON"
},
"text": "<font color=\"#80e27e\">Online</font>"
}
},
{
"decoratedText": {
"startIcon": {
"knownIcon": "PHONE"
},
"text": "+1 (555) 555-1234"
}
},
{
"buttonList": {
"buttons": [
{
"text": "Share",
"onClick": {
"openLink": {
"url": "https://example.com/share"
}
}
},
{
"text": "Edit",
"onClick": {
"action": {
"function": "goToView",
"parameters": [
{
"key": "viewType",
"value": "EDIT"
}
]
}
}
}
]
}
}
]
}
]
}
}
]
}'- Update placeholders with your data sources, notification preferences, and formatting.
- Add more data points from your sheet to the message content.
- Implement conditional logic to trigger notifications based on specific cell values or changes.
-
Error Handling: Implement robust error handling to gracefully manage exceptions and failures during notification delivery.
-
Enhanced Trigger Options: Explore additional trigger options like cell value changes or specific criteria for notifications.
-
Integration Expansion: Extend integration possibilities to include other communication platforms such as SMS, Slack, or custom webhooks.
-
Clone the repository: git clone [email protected]:harshad-kadam/Excel-Google-Chat-Integration.git
-
Create feature_yourname branch & git checkout to feature branch
-
Open folder with VSCode
-
Add your changes
-
Git commit & push changes
-
Create PR n share on [email protected]
-
I will merge to main code.
-
You did it. 🏆
-
Join Our channel https://t.me/apigeedeveloper
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent