Sauda•2mo ago

Using Voiceflow to write user response to Gsheet using API block

Hi all, I'm super new to the community as well as any level of coding and AI. This may be super straightfoward for some and I sincerely appreciate your help. What I want to do: Step 1: Voiceflow chat will prompt user to enter name Step 2: User enters name Step3: Write name to google sheet, with timestamp. Here's what I have set up: Step 1: Super straight forward with Prompt Block. Step 2: User enters name: I have a Capture block that saves the entire reply to 'username'. I believe this is a preset variable (I don't seem to have the ability to change anything about this variable) Step 3: Write to Google Sheet: A. Apps Script: I used the Apps Script to be able to POST data into the Gsheet: Code here const SHEET_NAME = 'Sheet1';
function doPost(e) { try { // Parse the incoming JSON const payload = JSON.parse(e.postData.contents); const userName = payload.username; //Assuming username is the variable in the payload. // Open the sheet and append the new row const SPREADSHEET_ID = 'IDRedacted'; // the ID from your sheet’s URL const ss = SpreadsheetApp.openById(SPREADSHEET_ID); const sheet = ss.getSheetByName(SHEET_NAME); sheet.appendRow([new Date(),userName ]); // Return a success response return ContentService .createTextOutput(JSON.stringify({ status: 'OK' })) .setMimeType(ContentService.MimeType.JSON); } } B. API Block in Voiceflow Type: POST URL from my deployed Apps Script Header: Content-Type Value: application/json no parameters Body - Raw - Json { "name": {username} } Question: In my Apps Script: When I have const userName = payload.username; Nothing gets written to the Gsheet. When I have const userName = payload.name; 0 gets written to the Gsheet, regardless of what user entered. How can I save the user's input in Voiceflow chat, into the Gsheet?

2 Replies

Tico | Voiceflow Assistant•2mo ago

➡️ What’s going wrong? Voiceflow only substitutes variables for VALUES, not for KEYS.
In the raw-JSON body you must keep the value in quotes and wrap the variable with curly braces. Eg:

{ 
  "username": "{username}"
}

{ 
  "username": "{username}"
}

If you send

{ "name": {username} }

{ "name": {username} }

Voiceflow tries to inject the variable without quotes, the JSON becomes invalid and Google Apps Script receives either undefined or the number 0. ➡️ Fix in three steps 1️⃣ Capture the user’s input
• Add a Variable called username in the Variables panel (if it doesn’t exist).
• In the Capture block map the user reply ➜ username. 2️⃣ API step configuration
• Method : POST
• URL : your Web-App URL from Apps Script
• Headers: Content-Type : application/json
• Body (raw-JSON):

{
  "username": "{username}"
}

{
  "username": "{username}"
}

(Test the request – you should see the correct JSON in the preview.) 3️⃣ Apps Script (no change needed except for the property name)

const SHEET_NAME   = 'Sheet1';
const SPREADSHEET_ID = 'ID_REDACTED';

function doPost(e) {
  const payload  = JSON.parse(e.postData.contents);
  const userName = payload.username;   // matches the JSON key above
  const ss   = SpreadsheetApp.openById(SPREADSHEET_ID);
  ss.getSheetByName(SHEET_NAME)
    .appendRow([new Date(), userName]);

  return ContentService.createTextOutput(
    JSON.stringify({ status: 'OK' })
  ).setMimeType(ContentService.MimeType.JSON);
}

const SHEET_NAME   = 'Sheet1';
const SPREADSHEET_ID = 'ID_REDACTED';

function doPost(e) {
  const payload  = JSON.parse(e.postData.contents);
  const userName = payload.username;   // matches the JSON key above
  const ss   = SpreadsheetApp.openById(SPREADSHEET_ID);
  ss.getSheetByName(SHEET_NAME)
    .appendRow([new Date(), userName]);

  return ContentService.createTextOutput(
    JSON.stringify({ status: 'OK' })
  ).setMimeType(ContentService.MimeType.JSON);
}

(Optional) If you prefer to keep the key name, just change both sides: • Body: { "name": "{username}" }
• Script: const userName = payload.name; 🎉 Run the assistant—after the user types their name you should see a new row in Google Sheets with the timestamp and the captured name. Docs reference: Voiceflow "API step → Headers, Body & Parameters" (https://docs.voiceflow.com)

SaudaOP•2mo ago

What I now want to do is: What I want to do: Step 1: Voiceflow chat will prompt user to enter name AND email Step 2: User enters name and email Step3: Write name and email to google sheet, with timestamp. Here's what I have set up: Step 1: Super straight forward with Prompt Block. Step 2: User enters name: I have a Capture block that saves the entities name and email. I have created these entities. I want to capture entities and not the entire user response. Step 3: Write to Google Sheet: A. Apps Script: I used the Apps Script to be able to POST data into the Gsheet: Code here const SHEET_NAME = 'Sheet1'; function doPost(e) { try { // Parse the incoming JSON const payload = JSON.parse(e.postData.contents); const userName = payload.username; const userEmail = payload.useremail; // Open the sheet and append the new row const SPREADSHEET_ID = 'IDRedacted'; // the ID from your sheet’s URL const ss = SpreadsheetApp.openById(SPREADSHEET_ID); const sheet = ss.getSheetByName(SHEET_NAME); sheet.appendRow([new Date(),userName , userEmail ]); // Return a success response return ContentService .createTextOutput(JSON.stringify({ status: 'OK' })) .setMimeType(ContentService.MimeType.JSON); } } B. API Block in Voiceflow Type: POST URL from my deployed Apps Script Header: Content-Type Value: application/json no parameters Body - Raw - Json { "username": "{name}”, "useremail": "{email}" } Question: When i do the capture entity block for name and email individually, this setup works. i.e Only have name in the json body or only have email in the json body, this workflow works fine and i can see the name or the email in the gsheet. However, when I try to do both at the same time, this workflow does not work and the gsheet is not updated.

Using Voiceflow to write user response to Gsheet using API block

Did you find this page helpful?