Write and Read Google Spreadsheet from Telegram bot with Google Cloud Functions

Recently I faced a request

I'm tracking the number of days off each person in my company in google sheets, where I manually add/subtract their remaining days off. Can a telegram bot show them how many days off they have left once they search for their names?

The answer is yes, and here is how to do that with Google Cloud Functions and API.chat chatbot platform in under 100 lines of code.

Setup Google Functions

First, you need to set up cloud functions and give them access to our document.

Overall you will need two functions: gsheet-get to provide answers and gsheet-add to register users. Let's create them.

We do not care about the function code yet. When you set up the first function GCloud will probably ask you to enable Billing and, most importantly, will create a Service account - App Engine default service account. Usage of this account will allow you to perform server to server, app-level authentication later.

But you do need to give access rights to our document to this account.

The document itself is quite simple

Function google-sheet-add

This function will store the user channel (telegram in the case), chat id, and a name to your table. Google Functions support different languages, including .net core 3.1, but I found out that node.js gives more compact code, so let's stick to it, and sorry for dotnetish code-style

const { google } = require('googleapis');

exports.main = async (req, res) => {
  let auth = await google.auth.getClient({ scopes: ['https://www.googleapis.com/auth/spreadsheets'] });  
  let sheets = google.sheets({ version: 'v4', auth });
  try
  {
    await sheets.spreadsheets.values.append({
      spreadsheetId: "INSERT-SHEET-ID-HERE",
      range: "Days!A:C",
      valueInputOption: "RAW",
      insertDataOption: "INSERT_ROWS",
      resource: {
        values: [
          [req.query.channel, req.query.id, req.query.name]
        ],
      },
      auth: auth
    });    
    res.status(200).send();
  }
  catch (err)
  {
    res.status(500).send({ err })
  }
};

The code here will append provided values as a new row to the Days sheet. API.chat will provide user data such as channel, chat id, and the name of the user.

Don't forget to add googleapis dependency into package.json

{
  "dependencies": {
    "googleapis": "^42"
  }
}

The main need for this function is to match actual people with their telegram account. There could be more sophisticated ways to do this, but for now this simple way: ask user name - is enough.

That will give you all the required information about the user and you could start calculating his day-offs.

Function google-sheet-get

The second function is for getting data from Google Spreadsheet.

const { google } = require('googleapis');

exports.main = async (req, res) => {
  const chatId = req.query.id;
  const auth = await google.auth.getClient({ scopes: ['https://www.googleapis.com/auth/spreadsheets'] });  
  const sheets = google.sheets({ version: 'v4', auth });
  try 
  {
    let sheet = await sheets.spreadsheets.values.get({
      spreadsheetId: 'INSERT-SHEET-ID-HERE',
      range: 'Days!A:D'
    });

    const rows = sheet.data.values;
    if (rows.length) {
      let finded = rows.find(el => el[1] === chatId)      
      if (finded === undefined) {     
        res.status(200).send({ CorrelationId: req.query.request, Messages: [ "Information not availible" ] });
      } else {      
        res.status(200).send({ CorrelationId: req.query.request, Messages: [ `You have ${finded[3]} days off` ] });
      }
    } else {
      console.warn('No data found.');
      res.status(200).send({ CorrelationId: req.query.request, Messages: [ "No data found" ] });
    }
    res.status(200).send();
  }
  catch  (err)
  {
    console.error('The API returned an error: ' + err)
    res.status(500).send({ err })
  }
};

The main thing I found out here is that google.sheets API filter does not allow you to filter on actual data - only on metadata. That's why you need to get the entire sheet and filter it in code. Like I said before, API.chat will provide your endpoint with chat-id for filtering. From there you can return a formatted string with the answer like that

{
  "Messages": [
    "You have 7 days off"
  ]
}

Chatbot Scenario

Now when you have all endpoints in place and working you can create an actual chatbot. I will use my very own chatbot builder API.chat. A scenario might be very simple, like that

<bot>
  <states>
    <state name="Start">
      <transition input="register" next="Registration">Please enter your name</transition>
      <transition input="*" next="Start" pending_keyboard="Register Me">Hello. Please Register first</transition>      
    </state>
    <state name="Registration">      
      <transition input="*" next="Registered" morphology="msg" action="https://us-central1-REDACTED.cloudfunctions.net/gsheet-add?name={msg}" no_stop="true">Welcome!</transition>
    </state>
    <state name="Registered">
      <transition input="days" next="Registered" action="https://us-central1-REDACTED.net/gsheet-get" pending_keyboard="Days Off"/>
      <transition input="*" next="Registered" pending_keyboard="Days Off">Press the button to get your Days off</transition>
    </state>
  </states>
</bot>

What we do here is register the user, asking his or her name, and pass it into gsheet-add action in query. After registration user stays in the Registered state forever with the ability to request their days-off through gsheet-get action at any time.

All that's left is to PUT this scenario to the chatbot scenario endpoint and our bot is ready to deliver.

curl -v -X PUT "https://bot.api.chat/v1/bots/botName/scenario"
-H "Content-Type: application/xml"
-H "Cache-Control: no-cache"
-H "Ocp-Apim-Subscription-Key: REDACTED"
--data-raw '<bot>
  <states>
    <state name="Start">
      <transition input="register" next="Registration">Please enter your name</transition>
      <transition input="*" next="Start" pending_keyboard="Register Me">Hello. Please Register first</transition>      
    </state>
    <state name="Registration">      
      <transition input="*" next="Registered" morphology="msg" action="https://us-central1-REDACTED.cloudfunctions.net/gsheet-add?name={msg}" no_stop="true">Welcome!</transition>
    </state>
    <state name="Registered">
      <transition input="days" next="Registered" action="https://us-central1-REDACTED.cloudfunctions.net/gsheet-get" pending_keyboard="Days Off"/>
      <transition input="*" next="Registered" pending_keyboard="Days Off">Press the button to get your Days off</transition>
    </state>
  </states>
</bot>'

Out tiny chatbot is ready

Let's count code lines

• google-sheet-add: 25
• google-sheet-get: 30
• scenario: 15
• overall: 70 lines of code

Not bad