IMPORTDATA In Google Spreadsheet and keep the leading zero

Question

I have a CSV file with certain entries being numbers and start with zeros, such as phone numbers and reference numbers. For instance:

"ID","Name","Telephone Number","Tracking Reference"
1,"John","030928374","098283746322"
2,"Maria","0049827326251","0011928376"

When I use the function IMPORTDATA in Google Spreadsheets, the leading zeros are systematically removed, but I need to keep them. I tried converting the columns to text, but it's still the same. Is there any way to import the data while keeping the initial zeros? Please note that the length of the entries can vary, as phone numbers have different formats across different countries, and reference numbers come from different suppliers.

I put the abovementionned CSV onto a test server, in order to make things easier: =IMPORTDATA("http://35.229.200.45/example.csv")

Answer 1

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.