Google Sheets setValues anomaly?-CodePudding

I have function, gatherAds which is attached to a button in a variety of worksheets in a Google Sheets workbook. It calls an ADS function which runs a library routine that talks to some middleware on our server to pull down Google Ads data. The result variable contains a 2D array which is injected into the workbook at row 9, column 2 (after clearing out anything that's already there.)

At the bottom the actual .setValues() is in a try/catch. That's because, even though it works, it still insists on throwing an error, namely: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 15. (or variations on that theme.)

Why does something that works also throw an error? And what's wrong with worksheet.getRange(9, 2, length, width).setValues(result); in any case?

function gatherAds() {
  const workbook = SpreadsheetApp.getActive();
  const worksheet = workbook.getActiveSheet();
  const B = [];
  for (let i = 0; i < 7; i  ) {
    B.push(worksheet.getRange('B'   (i   1)).getDisplayValue());
  }
  const result = ADS(...B);
  const length = result.length;
  const width = result[0].length;

  const range = worksheet.getRange(9, 2, worksheet.getLastRow(), worksheet.getLastColumn());
  range.clear();
  SpreadsheetApp.flush();

  try {
    worksheet.getRange(9, 2, length, width).setValues(result);
  } catch (E) {
    console.error(E.message);
  }
}

/**
 * ADS Calls GoogleAds 
 * @param  {string} customerId
 * @param  {string} selectClauseFields
 * @param  {string} fromClause
 * @param  {string} whereClause
 * @param  {string} orderByClause
 * @param  {string} limitClause
 * @param  {string} parametersClause
 * @customfunction
 */
function ADS(customerId, selectClauseFields, fromClause, whereClause, orderByClause, limitClause, parametersClause) {
  return DaisyChain.ADS3(customerId, selectClauseFields, fromClause, whereClause, orderByClause, limitClause, parametersClause);
}

CodePudding user response：

Try it this way:

function gatherAds() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const B = sh.getRange("B1:B7").getDisplayValues().flat();
  const result = ADS(...B]);
  const length = 1;
  const width = result.length;
  const range = sh.getRange(9, 2, sh.getLastRow(), sh.getLastColumn());
  range.clear();
  SpreadsheetApp.flush();
  try {
    sh.getRange(9, 2, 1,result.length).setValues([result]);
  } catch (E) {
    console.error(E.message);
  }
}

CodePudding user response：

It's possible the result array is a ragged array, with rows that are not uniform in length. I can reproduce both aspects of your error (the same error message and data gets written to the sheet) with the following snippet:

let grid = [
    [1,2,3],
    [4],
    [5,6,7]
  ]
SpreadsheetApp.getActiveSheet().getRange(1,1,3,3).setValues(grid)

The first row ([1,2,3]) is written to the sheet, and the error is thrown:

Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 3.

I suspect that, internally, Apps Script is writing one row of the array at a time, so only after it writes the first row does it notice that the following row does not conform to the dimensions of the range.

In your case, you might have some extraneous rows at the end of result, so it appears that result is being written, but it's really only partially so. You can verify this with

console.log(JSON.stringify(result, null, 2))

before you call .setValues()