"Error parsing int value [#N/A]" when querying a Google Spreadsheet using CDATA

Question

We are trying to query a Google Spreadsheet that has some fields with #N/A in them.

#N/A errors in Google Spreadsheet indicate missing information and signal functions to cease calculation.

We just want want these #N/A values to be treated as NULLs.

What is the best way to do this using the CData connector for Google Spreadsheet?

Answer 1

I presume setting your formulas to ifError isn't an option in your spreadsheet? That seems like the most sensible. Example, =iferror(vlookup(foo),) returns null instead of #N/A.

Assuming not, here's an app script that could review your data and replace values, or create a new sheet in your spreadsheet with updated values.

function fixErrorsInSheet(_sh) {
  const errorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A"];
  if (_sh === undefined) {
    _sh = SpreadsheetApp.getActiveSheet();
  }
  const ss = _sh.getParent();
  var dataRange = _sh.getDataRange();
  var allValues = dataRange.getValues();
  for (var i = 0; i < allValues.length; i++) {
    for (var j = 0; j < allValues[i].length; j++) {
      var oneValue = allValues[i][j];
      if (errorValues.includes(oneValue)) {
        allValues[i].splice(j, 1, null);
      }
    }
  }
  //optional to update sheet, or you could query this array directly with cData;
  dataRange.setValues(allValues);

  //create new sheet with updated values...
  //ss.setActiveSheet(_sh);
  //var newSheet = ss.duplicateActiveSheet();
  //newSheet.getRange(1,1,dataRange.getLastRow(),dataRange.getLastColumn()).setValues(allValues);
}

//use this to run on all sheets
function runFixOnAllWorkbooks() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(fixErrorsInSheet);
}