fractions turning to dates in sheets

Question

I have data with values 5/8 and 3/4 that are provided in .csv file.

Example:

Raw CSV data: [Cat, 1, 5/8], [Dog, 2, 5/8], [Tiger, 3, 5/8]

I take the csv and parse using:

var csv = Utilities.parseCsv(text, ',');    
ss.getRange(1, 1, csv.length, csv[0].length).setValues(csv);

values that stored as 5/8 and 3/4 are being changed to dates, 5/8/2021

In sheets:

[Cat, 1, 5/8/2021], [Dog, 2, 5/8/2021], [Tiger, 3, 5/8/2021]

• is there a way to have 5/8 remain as a string and not treated as a number/date?

Answer 1

You can change the format of your range before setting its value.

Sample Code:

  var url = 'https://fractiondata22.s3.ca-central-1.amazonaws.com/test_data_20210413.txt';
  var text = UrlFetchApp.fetch(url).getContentText();
  var csv = Utilities.parseCsv(text, ','); 
  
  var range = ss.getRange(1,1,csv.length,csv[0].length);
  range.setNumberFormat("@");
  range.setValues(csv);

What it does?

• Select the range based on the converted csv file data
• Change the format of the range using Range.setNumberFormat(numberFormat). Use @ as number format token

@

Inserts the raw text for the cell, if the cell has text input. Not compatible with any of the other special characters and won’t display for numeric values (which are displayed as general format).

Output: