CODEWITHSUNDEEP
excelcsvgoogle-sheets
user1721182
user1721182

Reputation: 171

How to Enclose an Entire Google Sheets .csv Column in Quotes?

I'm currently trying to enclose an entire column in a Google Sheets CSV file in double quotes for a script that I'm writing. I've attached a link for the sample CSV sheet below:

https://docs.google.com/spreadsheets/d/1qhXEG_IxBzjTDkq0r2L-9MTAM6iO1L7qDFJCnkcI4n4/edit?usp=sharing

When I export this sheet then the first cell in the 2nd row will be enclosed in quotations due to the special characters, but the second cell will not be as it does not have any special characters. For consistency when reading from a cell I want entries in the second column to be enclosed in double quotes.

I'm hoping for something simple such as changing a setting in the Google Sheets or potentially a function as I'm trying to make my script as dummy proof and simple for non-tech users. Is there any way to do this?

Upvotes: 0

Views: 588

Answers (1)

Century Tuna
Century Tuna

Reputation: 1762

You can run this function before exporting the content of your sheets to enclose the entire 2nd column with double quotes:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1,2,sheet.getLastRow(),1); 
  var values = range.getValues();
  var quoted = values.map(x => x.map(y => '"' + y + '"'));
  
  range.setValues(quoted);
}

Screenshot:

enter image description here

UPDATED Ver:

I added a ternary operator which works if we'll be adding a single if-else like condition for arrow functions in javascript.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1,2,sheet.getLastRow(),1); 
  var values = range.getValues();
  var quoted = values.map(x => x.map(y => y[0] == '"' && y[y.toString.length] == '"' ? y : '"' + y + '"' ));
  range.setValues(quoted);
}

Upvotes: 2

Related Questions