Introduction.
Developing “Mia,” a talking cat-shaped robot that speaks in various dialects.
We have decided to embed the following purchase survey Google form into the purchase completion screen from our website and would like to set up Slack to receive a notification when the form is submitted.
So, I would like to use GAS and the Slack API to set up automatic forwarding of submissions to Slack when submitting Google Forms.
Get Slack Webhook URL
Obtain a webhook URL to send data to Slack.
Access the Slack API and create an app.
Create a new Slack app
Click “Create New App” and specify an appropriate name and workspace.
Select the Slack workspace from the pull-down menu where you want to post the contents of the Google Spreadsheet.
display_informationname: Define the name of the app. This name will be displayed in the Slack workspace. In this case, we will list it as Google Forms.
Enable Incoming Webhooks
Enable “Incoming Webhooks” on the settings page of the app you created.
Click on “Add New Webhook to Workspace,” select the channel you want to post a message to, and generate a URL.
When I thought I could do this, I got an error message saying I did not have permission.
This is because the bot’s name is blank in the default settings, so the error occurs. I think I set the name of the app in the default settings…
Name the Bot
So, click on “App Home” from the left sidebar of the Slack API administration page.
The bot name input screen will open and you can enter a name for the bot.
The default username is the name used when searching for the bot or sending mentions. Only single-byte alphanumeric characters are accepted.
After adding, click Incoming Webhooks, then click Add New Webhook to Workspace again
This time, the channel selection screen appeared successfully.
Copy the Webhook URL
Copy the generated URL (e.g., https://hooks.slack.com/services/XXXX/YYYY/ZZZZ) and note it. This is the URL that will be used in GAS, which will be set up later.
Google Spreadsheet and Google Apps Script setup
Set up a script in the spreadsheet where Google Form responses are transcribed.
Open the spreadsheet where the Google Form responses will be saved, and select Extensions > Apps Script from the spreadsheet menu to open the GAS screen
Write scripts for posting to Slack
Copy and paste the following code into the Apps Script editor.
Set SLACK_WEBHOOK_URL to the Slack webhook URL obtained from the Slack API admin screen earlier.
const SLACK_WEBHOOK_URL = "https://hooks.slack.com/services/XXXXXXXX";
function postToSlack(e) {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
const data = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
// メッセージの構築
const message = `新しいGoogleフォームの回答がありました:n${data.join('n')}`;
const payload = {
const payload = {
text: message
};
const options = {
method: "post",
contentType: "application/json",
payload: JSON.stringify(payload)
};
UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options);
} catch (error) {
// エラーログを記録
Logger.log("Error posting to Slack: " + error.message);
// 必要であれば通知やアラート処理を追加
SpreadsheetApp.getUi().alert("Slackへの投稿でエラーが発生しました: " + error.message);
}
}
Trigger settings
Save the script on the editor (name is optional), open the trigger screen, and select “On form submission” as the event type.
operation check
We entered the data into the Google form, submitted it, and confirmed that the message was successfully posted to the designated Slack channel.
Bug and error inquiries should also be forwarded to the Slack CS channel each time, so that others, including developers, can be immediately aware of the content, so this should also be set up.
Since multiple webhook URLs can be created, you can implement this as well by creating a new webhook URL for the desired channel and pasting the generated URL into the GAS of another google spreadsheet (in this case, a bug or error inquiry).
