Discord Bot Unable To Get Access To The Google Sheets Getting Error The Request Is Missing A Valid API Key

I am having problem with my Discord bot trying to access the Google Sheets API v4.0. I want it to read, write, and update data in the sheet. My code work fine for Node.js applicati

Solution 1:

When you tried it in Node.js without Discord.js, you didn't export anything and you simply called gsrun(client) after the authorisation was successful, so it worked fine. The problem is that now you try to use getData without any authorisation. Although you have client.authorize in your code, you never use it.

To solve this, I would make at least two different functions here; one for generating the client and one for the get request itself and export them both.

To generate a client I’d wrap this in a promise. This way I could use async/await later. This function will create a client with the JWT, perform the authorisation, and either resolve with the client or reject the promise depending on the results of client.authorize().

function connect() {
  return new Promise((resolve, reject) => {
    const scope = ['https://www.googleapis.com/auth/spreadsheets'];
    const { client_email, private_key, private_key_id } = keys;
    const client = new google.auth.JWT(
      client_email,
      private_key_id,
      private_key,
      scope,
    );

    client.authorize((err) => {
      if (err) {
        reject(err);
      } else {
        resolve(client);
      }
    });
  });
}

Now you can simply connect and get the client by using const client = await connect().

The second part is to get some data from the spreadsheet. Again, I'd wrap it in a promise. This function will accept the client (we’ve just created above) and the options with the spreadsheetId and range. Inside the promise you just call the API endpoint with the options:

function getData(client, options) {
  return new Promise((resolve, reject) => {
    const endpoint = google.sheets({ version: 'v4', auth: client });

    endpoint.spreadsheets.values.get(options, (err, data) => {
      if (err) {
        reject(err);
      } else {
        // or resolve with data.data.values if you only want the values
        resolve(data.data);
      }
    });
  });
}

You can export both of these in an object. Here is the full sheet.js file:

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

const keys = require('./keys.json');

function connect() {
  return new Promise((resolve, reject) => {
    const scope = ['https://www.googleapis.com/auth/spreadsheets'];
    const { client_email, private_key, private_key_id } = keys;
    const client = new google.auth.JWT(
      client_email,
      private_key_id,
      private_key,
      scope,
    );

    client.authorize((err) => {
      if (err) {
        reject(err);
      } else {
        resolve(client);
      }
    });
  });
}

function getData(client, options) {
  return new Promise((resolve, reject) => {
    const endpoint = google.sheets({ version: 'v4', auth: client });

    endpoint.spreadsheets.values.get(options, (err, data) => {
      if (err) {
        reject(err);
      } else {
        resolve(data.data);
      }
    });
  });
}

module.exports = { connect, getData };

Then, you can import it in your bot's file and use it in there to connect first and then get the values:

const { Client } = require('discord.js');
const { connect, getData } = require('./sheet');

const client = new Client();

client.on('message', async (message) => {
  if (message.author.bot) return;

  const auth = await connect();

  const options = {
    spreadsheetId: '1LzdhD4rb2FdElTyQCAkgb5oyeGeu0d9rT2abS4n_4i8',
    range: 'A2:B5',
  };
  const { values } = await getData(auth, options);

  message.channel.send(values);
});