Why is Google Sheets query not suppressing header, when there's a calculated field?-CodePudding

I do a simple query on this table,

Row 1	Row 2	Row 3
1	2	3
4	5	6
7	8	9
10	11	12
13	14	15

with a calculated column (it is a column that does not exist in the Spreadsheet with the fixed "C" value, that I managed to create with upper('c') value.

If I want to filter out the header, it does not work. (if I take out the calculated column, it works again). To take out header I use the url "headers=0" param.

Version with the calculated column:

function testSQL() 
{
  const fileKey="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
  const sheetName="Sheet1";
  const formatType="csv";
  
  var queryA="SELECT A, B, C, upper('c')";
  var queryURLA = 'https://docs.google.com/spreadsheets/d/'   fileKey   '/gviz/tq?tqx=out:'   formatType   '&headers=0&sheet='   sheetName   '&tq='   encodeURIComponent(queryA);

  //Logger.log(queryURLA);

  var retA = UrlFetchApp.fetch(queryURLA, {headers: {Authorization: 'Bearer '   ScriptApp.getOAuthToken()}}).getContentText();
  var retAV = Utilities.parseCsv(retA,",");

  Logger.log(retAV);

  return retAV;
}

Execution log (Wrong! a strange header appears)

7:35:20 AM  Notice  Execution started
7:35:21 AM  Info    [[, , , upper("c"())], [1, 2, 3, C], [4, 5, 6, C], [7, 8, 9, C], [10, 11, 12, C], [13, 14, 15, C]]
7:35:21 AM  Notice  Execution completed

Version without the calculated column:

function testSQL() 
{
  const fileKey="xxxxxxxxxxxxxxxxxxxxxxxxx";
  const sheetName="Sheet1";
  const formatType="csv";
  
  var queryA="SELECT A, B, C";
  var queryURLA = 'https://docs.google.com/spreadsheets/d/'   fileKey   '/gviz/tq?tqx=out:'   formatType   '&headers=0&sheet='   sheetName   '&tq='   encodeURIComponent(queryA);

  //Logger.log(queryURLA);

  var retA = UrlFetchApp.fetch(queryURLA, {headers: {Authorization: 'Bearer '   ScriptApp.getOAuthToken()}}).getContentText();
  var retAV = Utilities.parseCsv(retA,",");

  Logger.log(retAV);

  return retAV;
}

Result (Correct, no header):

Execution log
7:31:59 AM  Notice  Execution started
7:31:59 AM  Info    [[1, 2, 3], [4, 5, 6], [7, 8, 9], [10, 11, 12], [13, 14, 15]]
7:32:00 AM  Notice  Execution completed

Offset does not solve the issue.

Does anybody confirm it is not my fault? Any solution / workaround?

CodePudding user response：

Add retAV.shift() before the line return retAV.

Example:

var retAV = Utilities.parseCsv(retA,",");
retAV.shift()
Logger.log(retAV);
return retAV;

Execution log

10:14:59 AM Notice  Execution started
10:15:00 AM Info    [[1, 2, 3, C], [4, 5, 6, C], [7, 8, 9, C], [10, 11, 12, C], [13, 14, 15, C]]
10:15:00 AM Notice  Execution completed