# Google Assistant Timesheet Action using Firestore

### 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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057088710/ae151f41-bf23-42c0-9bda-673daf209509.png align="left")

I have already covered how to fetch data from Google Sheets to Firestore using the Apps script check out this [link to know more](https://levelup.gitconnected.com/import-data-from-google-sheets-to-firestore-using-google-apps-script-b6f857f82a2?source=your_stories_page---------------------------). 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

1. What is Google Assistant
    
2. What is Cloud Function
    
3. Create a Google Action and integrate Dialogflow
    
4. Create intents in Dialogflow
    
5. Create Cloud functions and integrate with Dialogflow
    
6. Test your action
    

### 1\. What is Google Assistant

[*Google Assistant*](https://assistant.google.com/#?modal_active=none) *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](https://cloud.google.com/functions/docs/) 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](http://console.actions.google.com/) in a new tab. You should be looking at a clean Actions console that resembles the following (If you are a new user :)

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057090942/935faa71-96fd-46cc-9880-0825a3b50180.png align="left")

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**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057092651/62ae7538-db36-46fd-8de2-a6c7dad12eee.jpeg align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057094126/4df3ec32-a12c-4042-8d09-c3d1ebcb3e66.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057095750/557e46d5-ffa8-4dc3-af41-53acb735abaa.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057097186/8ffb9fc3-495c-44c4-9868-2d712450304e.jpeg align="left")

Now Select **Custom intent** and click **Build**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057098685/e70edead-641f-4781-94f7-5455152c184a.jpeg align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057100288/ab0b4b57-4aaa-498e-9b3c-ebd706c7fc51.png align="left")

### 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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057101891/163e1acd-4d6b-4679-bb66-5d38cfd1abef.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057103441/fab67c42-94be-4528-9257-a5754f6b8bba.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057105223/d64844ae-b964-4b4b-be7f-62633cbb77d4.png align="left")

**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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057107339/1fdd70d0-cdbb-439c-b9a1-f451b96abf4e.png align="left")

#### Entries

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057109653/9ac4b8c3-4db6-4d9d-a3c7-308990279a99.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057110675/cb38b038-58f4-45df-ba22-a764d142794d.png align="left")

#### Individual Entries

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057112238/63419303-f875-4fae-a22a-8c1e0771eb3e.png align="left")

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
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057113739/b9f63553-1583-4b70-8603-b313a3b6120e.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057115314/b25aec77-fbfc-4114-b217-820830f7ae40.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057116523/aab0d280-26fd-4b69-b791-99312096dba0.png align="left")

#### Team

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057117832/c5bebcf6-0a92-4c57-ac2b-c46a2aa319d7.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057118957/589b93b8-89a6-4f0b-b1fa-f2dda187e5f8.png align="left")

#### Projects

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057120497/d3354289-2621-46a9-89a4-733a1dddedbe.png align="left")

Project Training Phrase

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057121752/eb499e38-8399-41a0-89b1-ac6edd7091d9.png align="left")

#### Exit

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057123021/ad156d60-352c-49fe-a5fc-ab54417add5d.png align="left")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057124066/e0607e0b-d653-43e9-86f5-69c8f4299cf1.png align="left")

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

```javascript
'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 :)

```javascript
'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

```javascript
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

```javascript
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

```javascript
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

```javascript
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

<iframe src="https://www.youtube.com/embed/wTewk-Wi9sc?feature=oembed" width="700" height="393"></iframe>

Source: Nidhinkumar

### Reference Links

Timesheet Google Sheet Structure

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698057125652/03334169-a288-4a4a-a349-65ba49aa6acf.jpeg align="left")

Google Sheets Structure

App scripts code for Timesheet

* Adding timesheet to Firestore — [Click here to view the code](https://gist.github.com/nidhinkumar06/31fbd3cbeb87b8914c81b30881fa3482)
    
* Adding team and project to Firestore — [Click here to view the code](https://gist.github.com/nidhinkumar06/9cedd067afcfb41178ec280afbf39fe2)
    
* Deleting timesheet, team, and project from Firestore — [Click here to view the code](https://gist.github.com/nidhinkumar06/e80252a3088154b891dcd7c8ecddb2c2)
    

### Congratulations!

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