How can I use specific cells in a Google sheets range?

Question

I'm creating a Discord bot. Whenever I use the command that this is linked to it works. However it displays all the data in all cells in the set range. How do I get a specific cell out of the range and use it?

The way I've been doing it is by replacing the range to a specific cell, but I feel like that isn't the most practical way to do this.

Can anyone help? I'm pretty new to JS so any tips would be greatly appreciated.

async function readCerts() {
  const sheets = google.sheets({
    version: 'v4',
    auth
  });
  const spreadsheetId = 'myIdHere';
  const range = 'Overview!B5:H60';
  try {
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId,
      range
    });
    const rows = response.data.values;
    return rows;
  } catch (error) {
    console.error('error', error);
  }
}

Answer 1

From your following reply,

I have a google spreadsheet linked to my bot, the cells that are in the range is B5-H60, I just want to use E20, F30, G50, and C10 how would i get that cells information?

I understood your actual expected result as follows.

• You want to retrieve the cell values from the cells E20, F30, G50, and C10 using Sheets API.

In this case, how about using "Method: spreadsheets.values.batchGet"? When this is reflected in your script, it becomes as follows.

Modified script:

In this modification, your function readCerts is modified. In this modification, in order to retrieve the cell values from E20, F30, G50, and C10, "Method: spreadsheets.values.batchGet" is used.

async function readCerts() {
  const spreadsheetId = "myIdHere";
  const sheetName = "Overview";
  const cells = ["E20", "F30", "G50", "C10"];

  const ranges = cells.map((c) => `'${sheetName}'!${c}`);
  const sheets = google.sheets({ version: "v4", auth });
  try {
    const response = await sheets.spreadsheets.values.batchGet({ spreadsheetId, ranges });
    const rows = response.data.valueRanges.reduce((o, { values }, i) => ((o[cells[i]] = values[0][0]), o), {});
    return rows;
  } catch (error) {
    console.error("error", error);
  }
}

• When this script is run, the value of rows is as follows.

  {
    "E20": "cell value of E20",
    "F30": "cell value of F30",
    "G50": "cell value of G50",
    "C10": "cell value of C10"
  }
  

• When you want to retrieve only values in order of ["E20", "F30", "G50", "C10"] as a 1-dimensional array, please modify const rows = response.data.valueRanges.reduce((o, { values }, i) => ((o[cells[i]] = values[0][0]), o), {}); as follows.

  const rows = response.data.valueRanges.map(({ values }) => values[0][0]);
  

Note:

• In this modification, it supposes that your client can access the Spreadsheet. Please be careful about this.

Reference:

• Method: spreadsheets.values.batchGet

Added:

About your following new question,

Ive actually ran into one more issue. When the cell is empty it gives me an error, is there any way to fix this? error TypeError: Cannot read properties of undefined (reading '0')

In this case, please modify the above script as follows.

From:

const rows = response.data.valueRanges.reduce((o, { values }, i) => ((o[cells[i]] = values[0][0]), o), {});

To:

const rows = response.data.valueRanges.reduce((o, { values = [[""]] }, i) => ((o[cells[i]] = values[0][0]), o), {});

or

const rows = response.data.valueRanges.reduce((o, { values }, i) => ((o[cells[i]] = values ? values[0][0] : ""), o), {});