When reading merged cells received from

Result:

Please see reference link below for more information:

• https://support.google.com/docs/thread/135735079/merged-cells-only-have-a-value-on-first-cell-rest-of-the-cells-act-as-if-they-were-empty?hl=en

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.)

CodePudding user response：

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:

• Method: spreadsheets.get