How do I parse text strings in Google Sheets cells to a CSV file with Google Apps Script?-CodePudding

I have trouble parsing strings in the cells of Google Sheets using Google Apps Script. I used the JavaScript method Array.indexOf, but failed to find a character that's present in the string in a cell. I tried to insert underscores between the letters of the string in the cells, but only one underscore was inserted at the beginning of the string in each cell.

Here is my code:

function testCSV() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheets()[0];
  const range = sheet.getDataRange();
  const values = range.getValues();
  let csvStr = "";
  Logger.log(values);
  for (let i = 0; i < values.length; i  ) {
    let row = "";
    for (let j = 0; j < values[i].length; j  ) {
      if (values[i][j]) {
        row = row   "_"   values[i][j];
      }
      row = row   ",";
    }
    row = row.substring(0, (row.length-1));
    Logger.log(row);
    csvStr  = row   '\n';
  }
}

This screenshot shows the Logger output.

I want to enclose strings in cells that have commas in them with double quotation marks, just like what shows when CSV files are saved and opened in text format. Does anyone have a solution for this problem?

CodePudding user response：

Use Array.map(), String.replace() and Array.join(), like this:

function test() {
  const text = SpreadsheetApp.getActiveSheet().getDataRange().getDisplayValues();
  const result = textArrayToCsv_(text);
  console.log(result);
}


/**
* Converts text to a CSV format.
* When the data looks like this:

  header A1       header B1                   header C1
  text A2         text with comma, in B2      text with "quotes" in C2

* ...the function will return this:

  "header A1", "header B1", "header C1"
  "text A2", "text with comma, in B2", "text with \"quotes\" in C2"

* Lines end in a newline character (ASCII 10).
*
* @param {String[][]} data The text to convert to CSV.
* @return {String} The text converted to CSV.
*/
function textArrayToCsv_(data) {
  // version 1.0, written by --Hyde, 20 June 2022
  //  - see https://stackoverflow.com/a/72689533/13045193
  'use strict';
  return (
    data.map(row => row.map(value => `"${value.replace(/"/g, '\\"')}"`))
      .map(row => row.join(', '))
      .join('\n')
  );
}