Reputation: 300
Here is the goal: To use Google Sheets data into Dialogflow as fulfillmentText. This is the current code (using Google Apps Script):
function doPost(e) {
// This code uses the Sheets Advanced Service, but for most use cases
// the built-in method SpreadsheetApp.getActiveSpreadsheet()
// .getRange(range).getValues(values) is more appropriate.
var ranges = ['Sheet1!B1'];
var result = Sheets.Spreadsheets.Values.batchGet('sheetId, {ranges: ranges});
Logger.log(JSON.parse(result));
//Transforms the read value from Sheets into JSON format
return ContentService.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
The first problem is when reading from a spreadsheet, instead of getting only the requested value, this is the output:
{valueRanges=[{majorDimension=ROWS, values=[[Content here...]], range=Sheet1!B1}], spreadsheetId=XXXYYYZZZ}
The information concerned to the spreadsheet is not necessary. Only the range content is needed. So far, its just not possible to clean out the spreadsheet part inside the outputted JSON. I've not found anything about this problem in the documentation.
The second problem is: after the content is converted to JSON in return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON)
the previous content is wrapped inside the values
field: https://developers.google.com/apps-script/guides/content#serving_json_from_scripts
{
"valueRanges": [
{
"range": "Sheet1!B1",
"majorDimension": "ROWS",
"values": [
[
"Note that we link to certain 3rd party products via affiliate or sponsored links"
]
]
}
],
"spreadsheetId": "XXXYYYZZZ"
}
Summarizing, the first problem is reading just the content from Sheets API instead of: sheetId
, range and majorDimension
... The second is to send the JSON value unwrapped from the values
field. This the the response format eligible in Dialogflow:
https://cloud.google.com/dialogflow/docs/fulfillment-webhook#text_response
Thanks in advance!!!
Upvotes: 1
Views: 185
Reputation: 201388
I believe your goal as follows.
You want to retrieve the content of "Note that we link to certain 3rd party products via affiliate or sponsored links"
from the following object. And you want to return the content like {"values": content}
.
{
"valueRanges": [
{
"range": "Sheet1!B1",
"majorDimension": "ROWS",
"values": [
[
"Note that we link to certain 3rd party products via affiliate or sponsored links"
]
]
}
],
"spreadsheetId": "XXXYYYZZZ"
}
For this, how about this answer?
Sheets.Spreadsheets.Values.batchGet
returns the parsed JSON object. So it is not required to use JSON.parse
.result.valueRanges[0].values
. And also, when the 1st element is retrieved, please use result.valueRanges[0].values[0][0]
.When your script is modified, please modify as follows.
From:Logger.log(JSON.parse(result));
//Transforms the read value from Sheets into JSON format
return ContentService.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
To:
var content = result.valueRanges[0].values[0][0];
var res = {values: content};
return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON);
result.valueRanges[0].values
instead of result.valueRanges[0].values[0][0]
.Upvotes: 2