CODEWITHSUNDEEP
node.jsgoogle-sheetsgoogle-sheets-api
Apoqlite
Apoqlite

Reputation: 289

How to include merged cell info when reading google sheets through `googlesheets` node library?

When reading merged cells received from batchGet and get using the googlesheets API, any merged cells show up as empty except for the top left cell of the merge, how do I make the batchGet or get functions return every merged cell as the same value of the topleft cell. I can't check the previous cells and assign values as that is very unreliable as the sheet has many empty nonmerged cells.

Suppose the variable for the result from get or batchGet is called rangeData (for this question assume this is the result from batchGet).

EDIT: Here is my use case:

const characterRes = await googleSheets.spreadsheets.values.batchGet({
    auth,
    spreadsheetId,
    ranges: characterRanges,
  });

using @Tanaike 's answer lead to merges with invalid indices, I don't know why, maybe because using the sheets(merges) field gives merges for the entire sheet and not just the ranges?

Here is the full github page, in case you want to know the very specific use case: https://github.com/ChrisMGeo/genshin-helper-data-scraper

Upvotes: 1

Views: 977

Answers (2)

Logan
Logan

Reputation: 2140

It is not an issue with the Googlesheets API and/or the batchGet and get. Also it is not that you are not getting the info for the other cells, you are getting the info but they are actually empty. This is the standard behaviour of Spreadsheets. Merged cells only have value on the first cell, the rest of the cells act as if they are empty. A workaround you can do is have a script that will mirror the values of the merged cells from the first cell.

Provided another sample below using Google Apps Script getValue individually from the merged cell range:

enter image description here

Result:

enter image description here

You can see here that it is empty for all the other cells aside from the first/top left cell.

Please see reference link below for more information:

Also as stated by KarlS in the reference link you can send this feature request to the developers using the in-product feedback tools. Feedback submitted there will go directly to the development team and the more people who request a feature like this the more likely it will be implemented.

For Docs/Sheets/Slides: You can send feedback from the Help menu > Help Sheets/Docs/Slides improve. (For some this might be "Report an issue" instead.)

Upvotes: 0

Tanaike
Tanaike

Reputation: 201653

Issue and workaround:

In your situation, when "Method: spreadsheets.values.get" and "Method: spreadsheets.values.batchGet" are used for the sheet including the merged cells, only the left side of the merged cells is retrieved. Unfortunately, it seems that this is the current specification. So, in this case, it is required to use a workaround for achieving your goal.

About I can't check the previous cells and assign values as that is very unreliable as the sheet has many empty nonmerged cells., when "Method: spreadsheets.values.get" and "Method: spreadsheets.values.batchGet" are used, I can understand your concern. But, when the merged cell ranges can be retrieved, I thought that your goal can be achieved. In this answer, I would like to propose this as a workaround.

In this workaround, the merged cell ranges are retrieved using "Method: spreadsheets.get". Using this, the empty cells in the merged cells are embedded. The sample script is as follows. From your node.js tag, I used googleapis for Node.js.

Sample script:

const sheets = google.sheets({ version: "v4", auth: auth }); // Please use your script here.
const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetName = "Sheet1"; // Please set the sheet name including the merged cells.


// 1. Retrieve merged cell ranges using "Method: spreadsheets.get".
const res1 = await sheets.spreadsheets.get({
  spreadsheetId,
  ranges: [sheetName],
  fields: "sheets(merges)",
});
const merges = res1.data.sheets[0].merges;

// 2. Retrieve values using "Method: spreadsheets.values.get"
const res2 = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: sheetName,
});
const values = res2.data.values;
console.log(values); // Here, you can see the retrieved values from Spreadsheet.

// 3. Embed the merged cells with the 1st value of each merged cell range.
if (merges && merges.length > 0) {
  merges.forEach(
    ({ startRowIndex, endRowIndex, startColumnIndex, endColumnIndex }) => {
      const v = values[startRowIndex][startColumnIndex];
      for (let r = startRowIndex; r < endRowIndex; r++) {
        for (let c = startColumnIndex; c < endColumnIndex; c++) {
          values[r][c] = v;
        }
      }
    }
  );
  console.log(values); // Here, you can see the result values.
}
  • When this script is run, when the merged cells are included in the target sheet, the empty cells in the retrieved values are embedded with the 1st value of the merged cell range.

Reference:

Upvotes: 1

Related Questions