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());
}
CodePudding user response:
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.