With the Apps Script extension in Google Sheets, you can integrate JavaScript code for automation.
After writing code in the Apps Script editor and setting triggers, the script operates as defined.
Functions such as getActiveSheet() and getColumn() are provided by default.
Below is an example code snippet that sends data from the title row and selected rows to a Slack message when a checkbox is checked:
// Enter the Slack Webhook URL here
var SLACK_WEBHOOK_URL = "${Webhook URL}";
// Function to send a message to Slack
function sendToSlack(message) {
var payload = {
"text": message,
"username": "${App Name}",
"icon_emoji": ":robot_face:"
};
var options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(payload)
};
UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options);
}
// Function triggered when a cell is edited
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
// Operates only on column I where checkboxes are located
if (range.getColumn() === 9 && (range.getValue() === true || range.getValue() === false)) {
var row = range.getRow();
var headers = sheet.getRange(1, 1, 1, 8).getValues()[0]; // Fetch headers from A1:H1
var values = sheet.getRange(row, 1, 1, 8).getValues()[0]; // Fetch A:H values of the selected row
// Operates only if all cells in the row have values
var isComplete = values.every(cell => cell !== "" && cell !== null && cell !== undefined);
if (isComplete && range.getValue() === true) {
// Format the data into a Slack message
var tableRows = headers.map((header, index) => `| ${header} | ${values[index]} |`).join("\n");
var message = `*All values in Row ${row} are complete:*\n${tableRows}`;
// Send the message to Slack
sendToSlack(message);
// Set the checkbox back to false to prevent duplicate submissions
range.setValue(false);
}
}
}