Google Sheets is a powerful cross-platform app with a rich set of formulas and the ability to integrate with other apps.
However, such functionality is often redundant for simple tasks, which makes the work not very convenient. This is especially true for mobile devices. So it would be cool to make it possible to work with tables through other interfaces.
One option is to create a Telegram bot. This bot will receive some data from user and write it as new row into Google Sheets table.
It can be hosted for free!
With zero cost, we get a convenient combination of 2 powerful tools.
Tables API
The first step is to look at the tables documentation: https://developers.google.com/sheets/api/quickstart/nodejs
You can found a very useful button on this page:
Click on that button to create a new project and download credentials file (credentials.json).
Create a new file package.json
{
"name": "spreadsheets-bot",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"googleapis": "^59.0.0",
"telegraf": "^3.38.0"
}
}
This package file contains 2 dependencies:
- googleapis - library for google api
- telegraf - Telegram Bot framework
You can run npm install to install this dependencies.
In the meantime, let's look at an example provided by Google.
const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
// If modifying these scopes, delete token.json.
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
const TOKEN_PATH = 'token.json';
// Load client secrets from a local file.
fs.readFile('credentials.json', (err, content) => {
if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
authorize(JSON.parse(content), listMajors);
});
...
Quickstart code solves the authorization problem to gain access to your personal tables, which is what we need.
However, it is advisable to immediately make a change: remove ".readonly" from the line
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
This will change readonly access to read & write.
You need to put your credentials.json into project folder, run this file (index.json) by node index.js, open given link in browser, obtain a key, put this key into console.
If everything is done correctly, a file token.json will be created. Next authorizationы will work using the token from this file. Your bot will have access to all of your spreadsheets.
Create a Telegram Bot
Open @botfather in Telegram and send command /newbot. Provide a name for bot and you will get access token.
For our example we can hardcode this token in the script index.js
const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
const { Telegraf } = require('telegraf'); // import Telegraf
// Telegram bot access token:
const BOT_TOKEN = '1234567890:a1b2Cg2cLR4LGO1EpvDHawYPVjunkW3Bfk9';
// spreadsheets scope: read & write
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
You can receive messages and send replies:
const bot = new Telegraf(BOT_TOKEN);
// Load client secrets from a local file.
fs.readFile('credentials.json', (err, content) => {
if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
authorize(JSON.parse(content), startBot);
});
function startBot(auth) {
bot.on('message', (ctx) => {
if (ctx.from.id !== '123456789') { // your user id
return ctx.reply('Access Denied');
}
console.log(ctx.message.text);
ctx.reply("Hi, " + ctx.from.id);
});
bot.launch();
}
Depending on the task, you can compose commands or a dialogue with the bot, in which you can use the ability to read and write from tables.
Here is an example of writing to a table:
...
const rowNumber = "2";
let values = [
[
"Formula:", // string value
"=128/2+ROUND(8/3;2)", // any formula
"User:",
"Ben",
'', // empty cell
]
];
const resource = {values};
let valueInputOption = [
'RAW', // as string
'USER_ENTERED',// will be parsed as user input
'USER_ENTERED',
'USER_ENTERED',
'USER_ENTERED',
];
const range = sheet + '!A' + rowNumber + ':E' + rowNumber;
const spreadsheetId = 'mKj7bEUzG7miu4m5nsBt4KWTM6IIgstwn9g1a7IvVwz0';
sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption,
resource,
}, (err, result) => {
if (err) {
// Handle error
console.log(err);
ctx.reply(err.toString());
} else {
ctx.reply("Data saved."); //reply from bot to user
console.log('%d cells updated.', result.data.updatedCells);
}
});
Upload your bot to cloud for free
For convenience, the bot must be running 24/7. Then you can use it from your phone even when all your computers are turned off.
One of the options is Free Trial on Google Cloud Platform:
https://console.cloud.google.com/
For me this is not charging anything. Because the load on the server is too low.
You need to setup a new minimal cloud VM with Ubuntu or what you like.
When you connect to your new server by ssh, you can install node, npm and run your bot.
I recommend using PM2 to run script.
# install node & npm
curl -sL https://nsolid-deb.nodesource.com/nsolid_setup_3.x | sudo bash
sudo apt install -y nodejs npm
# install pm2
sudo npm install pm2@latest -g
Simple upload your script with rsync from local host to google cloud host:
rsync -ruv LOCAL_PROJECT_PATH SERVER_HOST:REMOTE_PATH
and start on the remote:
cd REMOTE_PATH
pm2 start index.js
Note that you should replace all my "uppercase" with your data (path to the project, server's ip, etc.)
Bot is started and you can use it 24/7
Top comments (0)