As best as I can establish, IMPORTDATA
won't respect leading zeros in ANY field containing numbers; the field will always be treated by IMPORTDATA
as numeric, regardless of sheet formatting. This applies regardless of whether IMPORTDATA
is used alone, or in conjunction with other functions such as QUERY
.
My proposed solution takes a different approach. Instead of using IMPORTDATA, I suggest importing, parsing and inserting the data into the spreadsheet. The benefits are that this approach enables display of leading zeros, and has the flexibility of enabling the data to be updated easily, and regularly.
1 - Format selected columns as text
First step is to identify the data columns in the CSV file that may contain leading zeros. Then format those spreadsheet columns as "Plain Text".
This does not help with IMPORTDATA; it just ignores cell/range formatting. However, cell formatting is respected when the CSV data is "inserted" into the sheet by other means.
This image shows what I mean. Using the sample csv data as a guide, select columns C & D, and then format as "Plain text".

2 - Import the data by script
This script imports the csv data and inserts it into the nominated sheet. Insert the script into code.gs in the script editor
/**
* Import, parse and insert the contents of the CSV data file.
*/
function importCSVfile() {
// establish the target sheet for the csv import
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("csvimport");
// clear existing
var range = sheet.getDataRange();
range.clear();
// Provide the full URL of the CSV file.
var csvUrl = "http://35.229.200.45/example.csv";
// get the csv data
var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
// parse the data
var csvData = Utilities.parseCsv(csvContent);
// paste the csv data into the sheet
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
If columns C & D are formatted as Plain text, then the data will appear formatted - both on the initial import and all subsequent imports.

If, per chance, Columns C & D are not formatted as Plain text, then the data is not formatted. After-the-event formatting does not repair leading zeros in the existing data, but if the data is reimported, then the "new" data will display leading zeros.
3 - Automating the CSV update
The IMPORTDATA
function automatically recalculates every hour (assuming the spreadsheet is open). So our remaining challenge is to trigger the function to run whenever the spreadsheet is opened and/or every hour while it is open.
/**
* Creates a time-driven triggers to update CSV
*/
function createCSVUpdateTrigger() {
// Trigger every 1 hours.
ScriptApp.newTrigger('importCSVfile')
.timeBased()
.everyHours(1)
.create();
}
/**
* Creates a trigger for when a spreadsheet opens.
*/
function createCSVUpdateonOpenTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('importCSVfile')
.forSpreadsheet(ss)
.onOpen()
.create();
}
Copy the code into your Code.gs and run both functions to create the triggers. Then from the Script Editor menu, check your Triggers: Edit > Current Projects Triggers. (FWIW, these were adapted direct from the Google documentation
Obviously you can adjust the update frequency to suit your own circumstances.

"002"
to"'002"
would work, but it's not really optimal, as I need to have then a CSV format just for google spreadsheet...IMPORTRANGE
function? What if you used the "import file" option from the menu?