Google sheets API fetch the value of Merged cells

Question

I am using Google Sheets API to display the data from my Google Spreadsheet in my javascript application. Have developed by refering this documentation. I could able to read the values from my spreadsheet but for some of the columns the cells are merged as below.

I am getting the Json response as

[
[
  "Name",
  "Age"
 ],
[
  "John",
  "25"
 ],
[
  "Doe"
 
 ],
]

My expected output should be

   [
[
  "Name",
  "Age"
 ],
[
  "John",
  "25"
 ],
[
  "Doe",
   "25"
 
 ],
]

Any help ?

score 1 · Answer 1 · answered Sep 12 '21 at 08:59

1

To retrieve the value, you have to test if the cell is part of a merged area.

var value = (cell.isPartOfMerge() ? cell.getMergedRanges()[0].getCell(1,1) : cell).getValue();

answered Sep 12 '21 at 08:59

Mike Steelson

14,650
2
5
20

Hi where to use this in code ? please help with js documentation sample . https://developers.google.com/sheets/api/quickstart/js – Jagadeesh Sep 12 '21 at 09:11
share your javascript application (simplified), it could be easier ... – Mike Steelson Sep 12 '21 at 09:17
It is exactly same index.html file given in docs. Only changes are my api credentials. – Jagadeesh Sep 12 '21 at 09:29
I think that you have also change row[4] to row[2], however, try my other answer or post your own script – Mike Steelson Sep 12 '21 at 17:02

score 0 · Answer 2 · answered Sep 12 '21 at 17:01

Modify the script as following

    appendPre('Name, Major:');
    var lastData=''
    for (i = 0; i < range.values.length; i++) {
      var row = range.values[i];
      if (row[4]!=''){lastData=row[4]}
      // Print columns A and lastData, which correspond to indices 0 and the last known value in column E that contains merged cells.
      appendPre(row[0] + ', ' + lastData);
    }

so that you will recall the last value if the cell is empty

Google sheets API fetch the value of Merged cells

2 Answers2