Dialogflow Fulfillment - Create a New Row in a Google Spreedsheet (Using Sheetdb io) - Here is the Code

Question

I'm trying to get my Dialogflow agent to create a new row with their contact information in a Google Sheet. I'm using Sheetdb.io to use the sheet as an API.

I have it so that it pulls information from the Google Spreadsheet, but I haven't been able to get it to create a new row.

Below is my code that I have right now. (I followed along a tutorial on YouTube by Axel Web Technologies)

function createHybridDataHandler(agent){
const{
    Email, FirstName, LastName
} = agent.parameters;

const data = [{
    Email: Email, 
    FirstName: FirstName, 
    LastName: LastName,
    Workout: Email,
    thefirstname: FirstName,
    thelastname: LastName
}];
axios.post('https://sheetdb.io/api/v1/hsb5ovk40x88i', data);
agent.add(`Done`);

}

This is the code I have for the intentMap.

intentMap.set ('CreateHybridData', createHybridDataHandler);

I have axios set up in both the index and package in Dialogflow. (Used axios to pull data from the Google Spreadsheet successfully)

Edit: Sorry I forgot to provide the errors I'm getting. Below you'll find the errors I'm encountering in Firebase when checking the logs.

dialogflowFirebaseFulfillment
Function execution started

> dialogflowFirebaseFulfillment
Warning, estimating Firebase Config based on GCLOUD_PROJECT. Initializing firebase-admin may fail


> dialogflowFirebaseFulfillment
Dialogflow Request headers: {"host":"us-central1-archie-personal-trainer-pueafg.cloudfunctions.net","user-agent":"Google-Dialogflow","transfer-encoding":"chunked","accept":"*/*","accept-encoding":"gzip,deflate,br","content-type":"application/json","forwarded":"for=\"66.249.84.223\";proto=https","function-execution-id":"e3y2hwjsi092","x-appengine-country":"ZZ","x-appengine-default-version-hostname":"b6b956e25660d8640p-tp.appspot.com","x-appengine-https":"on","x-appengine-request-log-id":"5ee6818900ff04307a9500ff68ef0001737e6236623935366532353636306438363430702d7470000139303866373866633530636138623435363966303164656633393333393136383a3334000100","x-appengine-user-ip":"66.249.84.223","x-cloud-trace-context":"47d4c8b63058cf02e15d32b293b42f1a/16021772533127728568;o=1","x-forwarded-for":"66.249.84.223","x-forwarded-proto":"https","connection":"close"}

dialogflowFirebaseFulfillment
Dialogflow Request body: {"responseId":"e5435eeb-249b-4f78-9a5a-0cd51b312807-2db64ae0","queryResult":{"queryText":"Jordan","parameters":{"Email":"cjgibson440@gmail.com","FirstName":"Michael","LastName":"Jordan"},"allRequiredParamsPresent":true,"outputContexts":[{"name":"projects/archie-personal-trainer-pueafg/agent/sessions/7f183f01-5ae4-830d-5db9-07d689eb2d02/contexts/__system_counters__","parameters":{"no-input":0,"no-match":0,"Email":"cjgibson440@gmail.com","Email.original":"cjgibson440@gmail.com","FirstName":"Michael","FirstName.original":"Michael","LastName":"Jordan","LastName.original":"Jordan"}}],"intent":{"name":"projects/archie-personal-trainer-pueafg/agent/intents/74086fa6-8d30-48a8-a439-397094c4ab7e","displayName":"CreateHybridData"},"intentDetectionConfidence":1,"languageCode":"en"},"originalDetectIntentRequest":{"payload":{}},"session":"projects/archie-personal-trainer-pueafg/agent/sessions/7f183f01-5ae4-830d-5db9-07d689eb2d02"}

dialogflowFirebaseFulfillment
Function execution took 2879 ms, finished with status code: 200

dialogflowFirebaseFulfillment
Unhandled rejection

dialogflowFirebaseFulfillment
Error: Request failed with status code 400 at createError (/srv/node_modules/axios/lib/core/createError.js:16:15) at settle (/srv/node_modules/axios/lib/core/settle.js:17:12) at IncomingMessage.handleStreamEnd (/srv/node_modules/axios/lib/adapters/http.js:236:11) at emitNone (events.js:111:20) at IncomingMessage.emit (events.js:208:7) at endReadableNT (_stream_readable.js:1064:12) at _combinedTickCallback (internal/process/next_tick.js:139:11) at process._tickDomainCallback (internal/process/next_tick.js:219:9)

Thank you for your help!

Welcome to StackOverflow! It would help if you updated your question to include any errors you got while running this, or any problems you encountered. If it isn't working, what *is* it doing? The more information you provide, the better our chances are of helping you. See [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) — Prisoner, Jun 14 '20 at 19:14
@Prisoner Thanks for the help, I just added all of the logs that I see in Firebase when I try to run it. This is my first post on Stack Overflow so if there is anything I did wrong I'll try and fix it ASAP. I appreciate you! — Casey Gibson, Jun 14 '20 at 20:06

score 0 · Accepted Answer · answered Jun 15 '20 at 13:58

The important bit in your error is the line

Request failed with status code 400

The Sheets DB status code documentation says that error code 400 indicates "API could not understand the request", which suggests that your data is formatted incorrectly.

The POST - create row API endpoint says that it is expecting a POST body with a JSON object containing a "data" field with an array of objects. Although you are sending an array of objects, these aren't part of an object with a data field.

Your code should probably look something more like:

const data = [{
    Email: Email, 
    FirstName: FirstName, 
    LastName: LastName,
    Workout: Email,
    thefirstname: FirstName,
    thelastname: LastName
}];
const body = {
  data
};
axios.post('https://sheetdb.io/api/v1/hsb5ovk40x88i', body);

Thank you so much! That worked! I really appreciate your answer. This was a huge help! :) — Casey Gibson, Jun 15 '20 at 19:00

Dialogflow Fulfillment - Create a New Row in a Google Spreedsheet (Using Sheetdb io) - Here is the Code

1 Answers1