Currently, the bot checks if your ID already exists in the sheet list and adds you if it doesn't when someone writes a message in the chat.
Now, I want the bot to implement a message counter in the sheet list. What would be the most effective way to achieve this with the existing code?
In short: The bot verifies your presence in the list and increments the message counter by +1 (where "Nachrichten" signifies Messages).
You can think of it as a database that records the number of messages from each user.
Below is the current code:
const fs = require('fs').promises;
const path = require('path');
const process = require('process');
const {authenticate} = require('@google-cloud/local-auth');
const {google} = require('googleapis');
const axios = require('axios');
const moment = require('moment');
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 = path.join(process.cwd(), 'token.json');
const CREDENTIALS_PATH = path.join(process.cwd(), 'credentials.json');
module.exports = client => {
client.on('messageCreate', async message => {
const member = message.member
async function loadSavedCredentialsIfExist() {
try {
const content = await fs.readFile(TOKEN_PATH);
const credentials = JSON.parse(content);
return google.auth.fromJSON(credentials);
} catch (err) {
return null;
}
}
/**
* Serializes credentials to a file compatible with GoogleAuth.fromJSON.
*
* @param {OAuth2Client} client
* @return {Promise<void>}
*/
async function saveCredentials(client) {
const content = await fs.readFile(CREDENTIALS_PATH);
const keys = JSON.parse(content);
const key = keys.installed || keys.web;
const payload = JSON.stringify({
type: 'authorized_user',
client_id: key.client_id,
client_secret: key.client_secret,
refresh_token: client.credentials.refresh_token,
});
await fs.writeFile(TOKEN_PATH, payload);
}
/**
* Load or request authorization to call APIs.
*
*/
async function authorize() {
let client = await loadSavedCredentialsIfExist();
if (client) {
return client;
}
client = await authenticate({
scopes: SCOPES,
keyfilePath: CREDENTIALS_PATH,
});
if (client.credentials) {
await saveCredentials(client);
}
return client;
}
/**
*
* @see https://docs.google.com/spreadsheets/d/12e_460eFvxYS5m8AMRg17uBlfEEzjpOfPqgbX0YIeEE/edit
* @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
*/
async function listMajors(auth) {
const sheets = google.sheets({version: 'v4', auth});
const res = await sheets.spreadsheets.values.get({
spreadsheetId: '12e_460eFvxYS5m8AMRg17uBlfEEzjpOfPqgbX0YIeEE',
range: 'Liste!B2:B',
});
const rows = res.data.values;
const rowsdata = res.data.values.join(', ');
if (rowsdata.includes(`${member.id}`)) {
// Here, I need the bot to edit the Message Line
return;
} else
axios.post('https://sheetdb.io/api/v1/gfycyawty283n', {
data: {
Name: `${member.user.tag}`,
ID: `${member.id}`,
Nachrichten: `1`,
VoiceTime: "00:00:00:00",
ServerBeigetreten: moment.utc(member.joinedAt).format('DD/MM/YY HH:MM:SS')
}
})
}
authorize().then(listMajors).catch(console.error);
})
};Sheet after modification:
"Nachrichten" represents Messages.
The bot needs to locate the user ID and increment the "Nachrichten" Value by +1 for every message sent (https://i.sstatic.net/MVsZB.png).