Overview

In my previous blog, I have explained how to create a timesheet action in Google by getting the data from Google Sheets.

In this blog, I will explain how to get the data from Firestore for the Timesheet action using Firebase Cloud Functions

Flow

I have already covered how to fetch data from Google Sheets to Firestore using the Apps script check out this link to know more. We will cover the second part on how to create an action in Google Assistant

In the above link, i have explained how to get data from Google Sheet to Firestore with COVID19 data. If u want the Google Sheets and App scripts code for the Timesheet scroll down to Reference Links section :)

Objectives

What is Google Assistant
What is Cloud Function
Create a Google Action and integrate Dialogflow
Create intents in Dialogflow
Create Cloud functions and integrate with Dialogflow
Test your action

1. What is Google Assistant

Google Assistant is a personal voice assistant that offers a host of actions and integrations. From sending texts and setting reminders, to ordering coffee and playing music, the 1 million+ actions available suit a wide range of voice command needs.

2. What is Cloud Function

Google Cloud Functions is a lightweight compute solution for developers to create single-purpose, stand-alone functions that respond to cloud events without the need to manage a server or runtime environment.

3. Create a Google Action and integrate Dialogflow

Regardless of the Assistant application, you’re building, you will always have to create an Actions project so your app has an underlying organizational unit.

Open the Actions on Google Developer Console in a new tab. You should be looking at a clean Actions console that resembles the following (If you are a new user :)

Click Add/import project and agree to Actions on Google’s terms of service when prompted.

Click into the Project Name field and add the project name standup sheet

Once the project is created you will see a welcome screen like below, scroll down and select Conversational

Once the Conversational is selected click the Invocation option at the left bar and set the Display Name as standup sheet

Once the Invocation name is given click Actions and select Get Started to Build your first action

Now Select Custom intent and click Build

It will now navigate to the Dialogflow console. In the Dialogflow console check whether the agent name is standup sheet or not

4. Create intents in Dialogflow

For this action, we will create the following intents

Timesheet-Forgotters
Entries
Individual Entries
Team
Projects
Exit

as well as we will use the Default intents available in Dialogflow.

Now click Create if the agent name is the same as your Action name. Once the agent is created on the left side you will see Intents. Click Intents

By default, you could see two intents namely

Default Welcome Intent
Default Fallback Intent

Click Default Welcome Intent

Now just scroll down you can see some Responses like the below image

Delete all the default responses and create a new response like Welcome and Enable Webhook call for the Default Welcome Intent and click SAVE

Now create a new Intent named Timesheet-Forgotters and in the Training Phases add the below phrases

Timesheet-Forgotters intent is used to see who has not filled the timesheet yesterday. Now scroll down and select Fulfilment section and Enable webhook call for the intent

Entries

Now create another intent named Entries and add the below Training phrases

Entries intent is used to see the filled timesheet. Now scroll down and select Fulfilment section and Enable webhook call for the intent

Individual Entries

Now create another intent named Individual Entries and add the Training phrases like below

In the Training Phase, you could notice some highlighters over the name and the day those are nothing but the Entities

In the above intent, there are two entities created one is the

System Entity
User Entity

In the above Actions and parameters, you can see the entity as @sys.date which is the system entity, whereas the other one @users is a user-defined entity.

To Create a user-defined entity click on the Entities and give a name for the entity and add the entities like below

Now click SAVE to the entity and go back to the Individual Entries intent and enable webhook call for the intent

Team

Now create another intent named Team and add the Training phrase like below

Now scroll down and select the Fulfilment section and Enable the webhook call for the intent

Projects

Now create another intent named Projects and add the Training phrase like below

Project Training Phrase

Now scroll down and select Fulfilment section and Enable webhook call for the intent

Exit

Now create the final Intent named Exit and add the Training phrases like below

Now scroll down and select Fulfilment section and Enable webhook call for the intent

Now we have completed the Dialogflow part by creating all the necessary Intents and Entities.

5. Create Cloud functions and integrate with Dialogflow

Now we will create the firebase cloud function, Go to your local terminal and create a new directory named standup sheet and navigate to that directory

Once navigated initialize Firebase Cloud Function using the command firebase init and then select Cloud Function and select the default project as standup sheet

Once the project is created Firebase CLI will create the below files and node_modules folder

index.js
package.json

Now install the actions-on-google plugin using the command npm i actions-on-google as well as some of the libraries needed for date conversion as well as time conversation

npm i hh-mm-ss — To convert seconds to hh:mm
npm i lodash
npm i moment
npm i chrono-node — To extract date from a text

Once these plugins are installed open the standup-sheet project in a text editor. Once opened create two directories named

database
prompts

as well as create an index.js file in each of the above folders

Once it is done open database -> index.js file and add the below code

'use strict';

const admin = require('firebase-admin');
admin.initializeApp();

module.exports = admin;

Click Save and open prompts -> index.js file and add the below prompts. The reason for moving the conversation to a commonplace is that we can use it anywhere don’t need to rewrite the dialogs again and again :)

'use strict';

module.exports = {
"welcome_new": "Welcome to Standup sheet.I will assist you to know what your team has done yesterday as well as the total hours spent on each project. Which one you would like to see",
"welcome_default": "Welcome to Standup sheet. What you want to see today",
"standup_options": "Entries, Team, Project",
"entries_dialog": "Getting the entries made by your team yesterday",
"no_entries": "Seems no one in your team has made filled the timesheet",
"timesheet_forgotters": "Here is the list of forgotters who has not filled the timesheet yesterday",
"timesheet_filled": "Great everyone in your team has filled the timesheet",
"team": "Here is your team",
"projects": "Here is the list of projects your team is handling",
"thankyou": "Thankyou, Have a nice day",
"database_error": "Sorry, something went wrong. Check back later."
}

Now open index.js file and import the plugins which are needed for the action like below

const functions = require('firebase-functions');
const moment = require('moment');
const TimeFormat = require('hh-mm-ss');
const lodash = require('lodash');
const chrono = require('chrono-node');

const admin = require('./database');

Now we will import the database needed for our action

const db = admin.firestore();
const timesheetCollectionRef = db.collection('timesheet');
const teamCollectionRef = db.collection('team');
const projectsCollectionRef = db.collection('projects');

Now import the prompts, actions-on-google and Dialogflow like below

const PROMPTS = require('./prompts');

const { dialogflow, Suggestions, Table } = require('actions-on-google');

const app = dialogflow();

Now we will write the logic for the Default Welcome Intent like below

app.intent('Default Welcome Intent', conv => {
if (conv.user.last.seen) {
conv.ask(`${PROMPTS.welcome_default}`);
} else {
conv.ask(`${PROMPTS.welcome_new}`);
}
conv.ask(new Suggestions(['Timesheet-Forgotters', 'Entries', 'Team', 'Projects', 'Cancel']));
});

Here we have added a condition whether the user is a new one or existing user based on the condition we are displaying the response.

You can get the rest of the intents logic from the below code.

Once the code is completed you can deploy it to Firebase using the command firebase deploy. Once your code is deployed you will get a Webhook URL.

Copy the webhook URL and paste the webhook URL in Dialogflow

6. Test your action

Now if you test your action you will see an output like below

Source: Nidhinkumar

Reference Links

Timesheet Google Sheet Structure

Google Sheets Structure

App scripts code for Timesheet

Adding timesheet to Firestore — Click here to view the code
Adding team and project to Firestore — Click here to view the code
Deleting timesheet, team, and project from Firestore — Click here to view the code

Congratulations!

You have learned how to create a Google Assistant Timesheet action by loading data from Firestore using Dialogflow. Happy Learning :)

Google Assistant Timesheet Action using Firestore