Connecting Dialogflow with Spreadsheets

index.js

'use strict';
 
const functions = require('firebase-functions');
const {WebhookClient} = require('dialogflow-fulfillment');
const {Card, Suggestion} = require('dialogflow-fulfillment');
const axios = require('axios');
const nodemailer = require("nodemailer");
const mysql = require('mysql');

const transporter = nodemailer.createTransport({
    service: 'gmail',
    auth: {
        user: 'EMAIL',
        pass: 'PASSWORD'
    }
});

 
process.env.DEBUG = 'dialogflow:debug'; // enables lib debugging statements
 
exports.dialogflowFirebaseFulfillment = functions.https.onRequest((request, response) => {
  const agent = new WebhookClient({ request, response });
  console.log('Dialogflow Request headers: ' + JSON.stringify(request.headers));
  console.log('Dialogflow Request body: ' + JSON.stringify(request.body));
 
  function getSpreadsheetData(){
  	return axios.get('https://sheetdb.io/api/v1/q75kzm7veg9m1');
  }
  
  function welcome(agent) {
    const name = agent.parameters.name;
    return getSpreadsheetData().then(res => {
    	res.data.map(person => {
            if(person.Name === name)
        	agent.add(`Here are the details for ${name}. Age: ${person.Age}, Email: ${person.Email}, Phone: ${person.Phone}`);
        });
    });
  }
 
  function fallback(agent) {
    agent.add(`I didn't understand`);
    agent.add(`I'm sorry, can you try again?`);
  }
  
  function saveDataHandler(agent){
    const {
    	name, email, phone, age
    } = agent.parameters;
    
    const data = [{
      Name: name,
      Age: age,
      Email: email,
      Phone: phone
    }];
    axios.post('https://sheet.best/api/sheets/0b0b9e28-a3cb-43d3-9291-eb89c6d82896', data);
  }
  
  function sendEmailHandler(agent){
    const { email, name } = agent.parameters;
    
    const mailOptions = {
        from: "Axlewebtech", // sender address
        to: email, // list of receivers
        subject: "Email from chatbot", // Subject line
        html: `<p> Hello ${name} </p>`
    };
    
    transporter.sendMail(mailOptions, function (err, info) {
        if(err)
        {
          console.log(err);
        }
    });
    
  }
  



  // Run the proper function handler based on the matched Dialogflow intent name
  let intentMap = new Map();
  intentMap.set('Default Welcome Intent', welcome);
  intentMap.set('Default Fallback Intent', fallback);
  intentMap.set('saveData', saveDataHandler);
  intentMap.set('sendEmail', sendEmailHandler);
  // intentMap.set('your intent name here', yourFunctionHandler);
  // intentMap.set('your intent name here', googleAssistantHandler);
  agent.handleRequest(intentMap);
});

package.json

{
  "name": "dialogflowFirebaseFulfillment",
  "description": "This is the default fulfillment for a Dialogflow agents using Cloud Functions for Firebase",
  "version": "0.0.1",
  "private": true,
  "license": "Apache Version 2.0",
  "author": "Google Inc.",
  "engines": {
    "node": "8"
  },
  "scripts": {
    "start": "firebase serve --only functions:dialogflowFirebaseFulfillment",
    "deploy": "firebase deploy --only functions:dialogflowFirebaseFulfillment"
  },
  "dependencies": {
    "actions-on-google": "^2.2.0",
    "firebase-admin": "^5.13.1",
    "firebase-functions": "^2.0.2",
    "dialogflow": "^0.6.0",
    "dialogflow-fulfillment": "^0.5.0",
    "axios": "0.19.0",
    "nodemailer": "6.3.1"
  }
}

Hello Anshul,

Is this going to work with Dialogflow API V2

yes absolutely. It will work with V2 API

Thank you for your respond.
I am experiencing a timeout using this method sometimes. specially if it was the first query in a while. did you experience the same? how I can solve that?

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