Question 236232 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Parse CSV number as text

I am trying to import the following CSV data into a Google Sheet, preserving the number format "0001,0002,0003" as a string.

CSV file

0001,0002,0003
test,test2,test3

I tried using Tanaike’s code:

Google Sheets Import CSV popup

function importCsv(e){
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
  }
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, csv.length, csv[0].length).setValues(csv);
}

But also the Batch Update one:
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

function importCsvbatchUpdate(e){
  
    if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
    }

var data = Utilities.parseCsv(Utilities.newBlob(...e).getContentText());

  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet();


var resource = {
  requests: [
    {
      pasteData: {
        data: data,
        coordinate: { sheetId: sheet.getSheetId() },
        delimiter: ","
      }
    }
  ]
};
Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

Both work as expected, but unfortunately, the "0001,0002,0003" in the CSV files are systematically populated as numbers "1,2,3" in Google Sheets.

Any ideas on how to keep the text/string formats of values with prefixed zeros?

Tags:

2

Answers


  1. 0

    In your situation, how about the following modification?

    Modified script:

    function importCsv(e){
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
  }
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, csv.length, csv[0].length).setNumberFormat("@").setValues(csv); // Modified
}
    • By this modification, the values are put into cells as strings. By this, 0001 is put as 0001.

    Note:

    • From as I understand it each element of the csv comes in the form of triplets like this "0001,0002,0003" in the Cooper’s comment, when I saw this question, I guessed that from Both work as expected, but unfortunately, the "0001,0002,0003" in the CSV files are systematically populated as numbers "1,2,3" in Google Sheets. and the sample CSV data, 0001,0002, and 0003 might be each cell. When "0001,0002,0003" is included in the CSV data, I think that 0001,0002,0003 will be put into a cell. But, if "0001,0002,0003" is included in your CSV data, please test the following modification. In this case, I think that both patterns can be used.

      function importCsv(e) {
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "sample");
    return;
  }

  const temp1 = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString()).map(r => r.flatMap(c => c.split(",")));
  const maxLen = Math.max(...temp1.map((r) => r.length));
  const csv = temp1.map((r) => [...r, ...Array(maxLen - r.length).fill(null)]);

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, csv.length, csv[0].length).setNumberFormat("@").setValues(csv);
}

    Reference:

    Login or Signup to reply.
  2. 0

    Please select Tanaike’s solution I that I had tried "@" but I guess I not. Any way this is my simplistic approach to testing and it seems to work.

    function myfunk() {
  let csv = '"0001,0002,0003","0004,0005,0006","0007,0008,0009"n"0001,0002,0003","0004,0005,0006","0007,0008,0009"n"0001,0002,0003","0004,0005,0006","0007,0008,0009"n"0001,0002,0003","0004,0005,0006","0007,0008,0009"n"0001,0002,0003","0004,0005,0006","0007,0008,0009"n"0001,0002,0003","0004,0005,0006","0007,0008,0009"n"0001,0002,0003","0004,0005,0006","0007,0008,0009"n';
  let arr = Utilities.parseCsv(csv, ",");
  Logger.log(JSON.stringify(arr));
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getRange(1, 1, arr.length, arr[0].length).setNumberFormat("@").setValues(arr);
}

    And this is what I got

    A B C
    1 0001,0002,0003 0004,0005,0006 0007,0008,0009
    2 0001,0002,0003 0004,0005,0006 0007,0008,0009
    3 0001,0002,0003 0004,0005,0006 0007,0008,0009
    4 0001,0002,0003 0004,0005,0006 0007,0008,0009
    5 0001,0002,0003 0004,0005,0006 0007,0008,0009
    6 0001,0002,0003 0004,0005,0006 0007,0008,0009
    7 0001,0002,0003 0004,0005,0006 0007,0008,0009

    So hopefully this will assist the op if not I’m sorry.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search