OnSubmit trigger help

[Alameen Rahman]

Hi guys,

Want to make a Slackbot that gives a Slack channel a notification every time a new submission is done on a Google form (ie new row is added to the associated sheet). Here is the code below:

function notifyTeamMembersOfNewRequest(event) {

const response = event.values

console.log(`response values: ${response}`)

let payload = {

text: response,

channel: 'C04AZ4GT2UU'

}

requestSlack('POST', `chat.postMessage`, payload)

}

function requestSlack(method, endpoint, params) {

const base_url = "https://slack.com/api/"

const headers = {

'Authorization': "Bearer " + 'xoxb-85054344165-4375824305110-C3V8yq8xiLfNyghxWyQHPZVG',

'Content-Type' : 'application/json'

}

const options = {

headers: headers,

method: method

}

let request_url

if (method == 'POST') {

request_url = base_url + endpoint

options.payload = JSON.stringify(params)

} else {

request_url = base_url + endpoint + params

}

const response = UrlFetchApp.fetch(request_url, options).getContentText();

const json = JSON.parse(response)

return {

response_code: json.ok,

response_data: json

}

}

While on the executions tab it says it's run fine, I'm not getting the notification on my desired slack channel.

Any help would be super - thanks!

[cwl...@gmail.com]

If you run the POST function by itself with dummy data, does it work ok? If the execution is running as expected (onSubmit trigger), then make sure your connection to Slack and POST to Slack is working as expected. 

While not Slack, here is a video for posting to Discord from Sheets:  https://www.youtube.com/watch?v=mrb_ZZ6e9eE

Good luck!

[Alameen Rahman]

Thanks for your reply - unfortunately have tried to modify it a little bit but to no avail. Could you be so kind and hold my hand through this a little bit?

[cwl...@gmail.com]

Do you have a company or paid account? If so, I would use the built-in Slack services (https://slack.com/help/articles/11086384874259-Use-Google-Sheets-with-Workflow-Builder).

I created my own test workspace and will see if I can figure it out from the Sheets side. I'm not sure if free accounts have access or not.

[cwl...@gmail.com]

Ok, this was pretty easy once I found a good example on Github. 

Please check out this Git:
https://github.com/markfguerra/google-forms-to-slack.

1) In your Slack workspace, create a new App  (Create from Scratch).  https://api.slack.com/apps?new_app=1

Give it a name like "Form-Bot" or something to identify it, and associate it with your desired destination workspace.

2) Once created, go to Add Features and Functionality so you can add a Webhook. 
Click on the toggle "Activate Incoming Webhooks"   then scroll to the bottom and click on "Add New Webhook to Workspace"

3) When the Permission dialog pops up, use the dropdown menu to select the actual #channel where the bot should post.

4) Back on the Webhook activation page, scroll down to see the new Webhook. Copy the URL and paste somewhere to hold it temporarily (e.g., Notepad)

5) Open your Sheet that has the Form attached and open the Script Editor. 

6) Either create a new script file, or comment out/replace the previous code. 

7) Paste the code.js content into the script editor.

8) Edit Line 11 to add your Webhook URL

9) Edit Line 14 to add your #channel

10) Run the initialize function in order to delete any triggers and add the appropriate "onFormSubmit" trigger. 

11) Submit a new Form response!  
If all was done correctly, the response showed up in your Slack channel. 

All the credit goes to markfguerra

[cwl...@gmail.com]

P.S., it also accepts and works with file uploads/attachments.

[Alameen Rahman]

Thank you so much for your help and linking me to the correct resource. As this has come from a code that I don't quite understand from top to bottom (however I will definitely read into all these in the coming weeks); I want it to remove some columns from the message as I don't want all of them to return. Any links/suggestions as to how to modify to remove these?

Thanks again!

[Alameen Rahman]

Actually I have since seen this: https://stackoverflow.com/questions/11947590/sheet-getrange1-1-1-12-what-does-the-numbers-in-bracket-specify, so will have a go at using this!