How can i write to a specific cell in a Google sheets range?

Question

I am creating a discord bot and would like to write to a specific cell in Google sheets. With this code i am able to post the userInput into the embed when the command is executed on discord.

However the code is returning an error : Missing range, and is not writing in google sheets.

Essentially i want the user to use the command "/checkbl steamidhere". "steamidhere" would be whatever the userInput is. Write that user input into the Google sheets cell H1.

This is my code I am currently using:

async sample() {
    const sheets = google.sheets({ version: "v4", auth });
    const spreadsheetId = "sheetid";
  
    const sheetName = "BOTDATA";
    const cells = ["H1"];
    const valueInputOption = "USER_ENTERED";
    const userInput = "sample value";
  
    const result = await sheets.spreadsheets.values.update({
      spreadsheetId,
      range: `'${sheetName}'!${cells[0]}`,
      resource: { values: [[userInput]] },
      valueInputOption,
    });
    console.log(result.data);
  }
  
  sample();

Answer 1

I believe your goal is as follows.

• You want to put a value userInput of const userInput = interaction.options.get('steamid').value; into the cell "H1" of "BOTDATA" sheet.

In this case, how about the following modification? In this modification, the function readBlacklist() is not used. Only the value userInput is put into the cell "H1" of "BOTDATA" sheet.

Modified script:

From:

const userInput = interaction.options.get('steamid').value;
const bl = new EmbedBuilder()

To:

const userInput = interaction.options.get('steamid').value;

// --- I added the below script.
const spreadsheetId = "myspreadsheetID";
const sheetName = "BOTDATA";
const cells = ["H1"];
const valueInputOption = "USER_ENTERED";
// const userInput = "sample value"; // This is a sample value. In your situation, "const userInput = interaction.options.get('steamid').value;" is used.
const result = await sheets.spreadsheets.values.update({
  spreadsheetId,
  range: `'${sheetName}'!${cells[0]}`,
  resource: { values: [[userInput]] },
  valueInputOption,
});
// console.log(result.data); // If you want to confirn the returned value, please use this.
// ---

const bl = new EmbedBuilder()

• When this script is run and your other parts work fine, the value userInput is put into the cell "H1" of "BOTDATA" sheet.

Note:

• In this modification, it supposes that your client sheets can be used for putting the values to the Spreadsheet using Sheets API. Please be careful about this.

Reference:

• Method: spreadsheets.values.update

Added:

From your following reply,

I've updated my original question with the modification, and the error i am receiving.

and, the following your new error,

Unfortunately i am getting TypeError: (intermediate value)(...) is not a function

Unfortunately, I cannot replicate your new error. I'm worried whether your new error is related to my script. So, in order to check it, please test the following script. In the additional script, only my script is used. If the same error occurs, please tell me.

async function sample() { // <--- Modified
  const sheets =   // Please use your client
  const spreadsheetId = "###"; // Please set your spreadsheet ID.

  const sheetName = "BOTDATA";
  const cells = ["H1"];
  const valueInputOption = "USER_ENTERED";
  const userInput = "sample value";

  // const sheets = google.sheets({ version: "v4", auth }); // Please remove this line.
  const result = await sheets.spreadsheets.values.update({
    spreadsheetId,
    range: `'${sheetName}'!${cells[0]}`,
    resource: { values: [[userInput]] },
    valueInputOption,
  });
  console.log(result.data);
}

sample();