Pull from Google Sheets

GET Request to pull data from Google Sheets API.
Created By
Max Reynolds
Voiceflow Built
INPUT VARIABLES
{
Spreadsheet URL
}
URL for the Google Sheet
{
Range
}
Range of data to pull
{
Google Sheets API Key
}
API key for your google sheet
{
}
{
}
{
}
OUTPUT VARIABLES
{
Content
}
Content pulled from your Google sheet
{
}
{
}
{
}
{
}
{
}
PATHS
{
Success
}
Successful data pull
{
Failure
}
Failed data pull
{
}
{
}
{
}
{
}

Function Code Snippet

 
export default async function main(args) {
  const { google_sheets_api_key, spreadsheet_link, range } = args.inputVars;

  if (!google_sheets_api_key || !spreadsheet_link || !range) {
    return {
      next: { path: 'error' },
      trace: [{ type: "debug", payload: { message: "Missing required input variables for Google Sheets API function" } }]
    };
  }

  const spreadsheetIdRegex = /\/d\/([a-zA-Z0-9-_]+)/;
  const match = spreadsheetIdRegex.exec(spreadsheet_link);
  if (!match) {
    return {
      next: { path: 'error' },
      trace: [{ type: "debug", payload: { message: "Invalid Google Sheets URL" } }]
    };
  }
  const spreadsheet_id = match[1];

  const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}/values/${range}?key=${google_sheets_api_key}`;

  try {
    const response = await fetch(url, { method: 'GET', headers: { 'Content-Type': 'application/json' } });
    const responseBody = response.json;

    if (!responseBody || typeof responseBody !== 'object') {
      return {
        next: { path: 'error' },
        trace: [{ type: "debug", payload: { message: "Invalid or missing response body from Google Sheets API" } }]
      };
    }

    // Simplifying the return object for testing
    return {
      outputVars: { sheetData: JSON.stringify(responseBody) }, // Temporarily return the response as a string
      next: { path: 'success' },
      trace: [{ type: "debug", payload: { message: "Response Body: " + JSON.stringify(responseBody) } }]
    };
  } catch (error) {
    return {
      next: { path: 'error' },
      trace: [{ type: "debug", payload: { message: "Error fetching data from Google Sheets: " + error.message } }]
    };
  }
}

Function Walkthrough