Google sheets v4 API returns empty array for a row containing no elements

Question

I am using google sheets api to get all the values across some columns (I am using nodejs). But, the issue I face is that, it returns empty arrays for rows with no elements. This makes it harder for parsing the data. Is there a way to an array with empty strings instead of empty arrays? I hope I can make this question clear by showing an example.

Suppose I have a sheet as follows:

   | A |    B   | C |    D   |
---+---+--------+---+--------+
 1 |   | Value1 |   | Value2 |
---+---+--------+---+--------+
 2 |   | Value3 |   |        |
---+---+--------+---+--------+
 3 |   |        |   |        |
---+---+--------+---+--------+
 4 |   |        |   | Value4 |
---+---+--------+---+--------+

When I use sheets.spreadsheets.values.get(), I get

[
    ['', 'Value1', '', 'Value2'],
    ['', 'Value3'],
    [],
    ['', '', '', 'Value4']
]

What I would like to get is

[
    ['', 'Value1', '', 'Value2'],
    ['', 'Value3', '', ''],
    ['', '', '', ''],
    ['', '', '', 'Value4']
]

Answer 1

Issue and workaround:

Unfortunately, when the method of spreadsheets.values.get in Sheets API is used, the values are as follows.

[
    ['', 'Value1', '', 'Value2'],
    ['', 'Value3'],
    [],
    ['', '', '', 'Value4']
]

It seems that this is the current specification. On the other hand, when the Spreadsheet service for Google Apps Script is used, your goal can be directly achieved. So in order to achieve your goal using Sheets API, in this answer, I would like to propose a workaround.

In this workaround, your goal is achieved by the post process of the retrieved values from the method of spreadsheets.values.get.

Sample script:

From your question, I thought that you are using googleapis for Node.js. The following sample script is for it.

const spreadsheetId = "###";  // Please set the Spreadsheet ID.
const range = "###";  // Please set the range.

const sheets = google.sheets({version: "v4", auth: auth,});
const request = {spreadsheetId: spreadsheetId, range: range,};
sheets.spreadsheets.values.get(request, (err, { data }) => {
  if (err) {
    console.log(err);
    return;
  }
  const max = data.values.reduce((c, e) => {
    const len = e.length;
    return c < len ? len : c;
  }, 0);
  const values = data.values.map((e) => {
    const len = e.length;
    return len < max ? e.concat(Array(max - len).fill("")) : e;
  });
  console.log(values);  // You can see the result value here.
});

• In this sample script, at first, the values are retrieved from Spreadsheet. And, the maximum length of column is retrieved from the retrieved values. And then, the empty elements are added for each row.

Result:

When above script is run for your sample Spreadsheet, the following result is obtained.

[
  [ '', 'Value1', '', 'Value2' ],
  [ '', 'Value3', '', '' ],
  [ '', '', '', '' ],
  [ '', '', '', 'Value4' ]
]

Note:

• In this answer, it supposes that you have already been able to get values from Google Spreadsheet using Sheets API.

Reference:

• Method: spreadsheets.values.get