Ad

Replace NULL Values When Importing CSV In A Spreadsheet

With this code I can import from a CSV to my sheet.

Now, I would avoid to display NULL values in the cells and replace them with an empty value.

What I could add to the code I use?

function testf() {
  var response = UrlFetchApp.fetch("https://xxx.csv");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dest = ss.getActiveSheet();
  SpreadsheetApp.flush();
  var req = { pasteData: { data: response.getContentText(), delimiter: ",", coordinate: { sheetId: dest.getSheetId() } } };
  Sheets.Spreadsheets.batchUpdate({requests: [req]}, ss.getId());
}
Ad

Answer

From Now, I would avoid to display NULL values in the cells and replace them with an empty value., if you want to replace NULL in the cells with the empty value, how about the following modification?

Modified script:

function testf() {
  var response = UrlFetchApp.fetch("https://xxx.csv");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dest = ss.getActiveSheet();
  SpreadsheetApp.flush();
  var sheetId = dest.getSheetId();
  var reqs = [
    { pasteData: { data: response.getContentText(), delimiter: ",", coordinate: { sheetId } } },
    { findReplace: { find: "NULL", replacement: "", sheetId } }
  ];
  Sheets.Spreadsheets.batchUpdate({ requests: reqs }, ss.getId());
}
  • In this modification, after the CSV was inserted, the value of NULL in the cells is replaced with "" using the batchUpdate method. In this case, this request can be run by one API call.
  • In your script, SpreadsheetApp.flush() might not be required to be used.

Reference:

Ad
source: stackoverflow.com
Ad