CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsslack-dialog
zz_helix
zz_helix

Reputation: 23

How can I get the information from slack dialog to store in google sheets?

I am trying to build a slack dialog that will accept responses from team members about what they are working on and store that information in google sheets. I have the dialog working, but I'm not sure how to capture the information in google sheets using Google App Script.

I have already tried the below code, but rather than getting any information in cell A1 like I expect, I just get the HTML response back in slack. 

function doGet(e) {  
  return ContentService.createTextOutput("I am alive");
}

function doPost(e) {    
  var params = e.parameter;
  var token = params.token;
  var text = params.text;
  var trigger_id = params.trigger_id;
  var slackUrl = "https://slack.com/api/dialog.open";

  if (typeof e !== 'undefined') { 
    var ss = SpreadsheetApp.openById(SHEET_ID);
    sheet.getRange(1,1).setValue(JSON.stringify(e)); 
  }

  var dialog = {
    "token": TOKEN, 
    "trigger_id": trigger_id,
    "dialog": JSON.stringify({
      "callback_id": "ryde-46e2b0",
      "title": "Submit a Slack-Update",
      "submit_label": "Update",
      "elements": [
        {
          "type": "text",
          "label": "Yesterday",
          "name": "yesterday",
          "placeholder": "What did you finish yesterday?"
        }
  }
  var options = {
    'method' : 'post',
    'payload' : dialog,
  }; 
  UrlFetchApp.fetch(slackUrl, options);
  return ContentService.createTextOutput("");
}

rather than getting my dialog to open and then recording the response, I am getting the following response in Slack:

<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>Error</title><style type="text/css">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text-align:center;font-family:monospace;margin:50px auto 0;max-width:600px">ReferenceError: &quot;sheet&quot; is not defined. (line 14, file &quot;Code&quot;, project &quot;Stand Up Form&quot;)</div></body></html>

I just need to know how to get the responses to each field of the dialog to appear in a google sheet. Right now I have everything appearing in cell A1, but realistically I want the user's name to appear in 1A, the date in 1B, and the responses in 1C-E.

I would also like to add some functionality to add the next response to the next empty row rather than always pasting in 1A. However, the main point of this post is simply to get the response to appear in google sheets.

I am a python programmer so I'm not very good in JavaScript.

Upvotes: 1

Views: 410

Answers (1)

0Valt
0Valt

Reputation: 10345

Problem

HTML response you get is actually an error page - if you inspect it closely (or, easier, parse it), you will see the exact reason why the issue occured:

ReferenceError: "sheet" is not defined

Solution

To solve it you need to access the sheet first (Spreadsheets in Google Docs are structured as: Spreadsheet -> Sheet -> Range).

Sample

A simple line should do the trick (note that since event object is always of type object if present, you can make a shortcut - undefined guard is redundant in a doPost() trigger function).

  var ss = SpreadsheetApp.openById(SHEET_ID);
  var sheet = ss.getSheetByName('yourSheetName');
  sheet.getRange(1,1).setValue(JSON.stringify(e));

I would also suggest storing only the parameter or parameters property values since e is a utility object and is dynamically constructed each time a request hits your application.

References

  1. getSheetByName() reference;
  2. getSheets() reference;

Upvotes: 2

Related Questions