1

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.

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?

My spreadsheet

doubleunary
  • 13,842
  • 3
  • 18
  • 51
maxloo
  • 453
  • 2
  • 12
  • `values` is a ``array`` of `arrays`, not a `string` – TheMaster Jun 20 '22 at 13:56
  • @TheMaster, I do not understand. I am parsing `values` as a 2D array. The link you suggested that my question duplicates did not address my specific question. – maxloo Jun 20 '22 at 14:07
  • If my question is rejected because of incomplete answers from another post, does that mean that there's no solution to my problem? – maxloo Jun 20 '22 at 14:08
  • It's not rejected. The point of linking to duplicates is to point you to better answers. You can always [edit] your question to appeal the duplicate suggestion. – TheMaster Jun 20 '22 at 14:09
  • @TheMaster, I have done so. – maxloo Jun 20 '22 at 14:15
  • Here: `let comma = (values.indexOf(",") > -1);`. `indexOf` will always be `-1` because you're comparing `string` against `array` type. Edited in a better duplicate - see if that makes sense. – TheMaster Jun 20 '22 at 14:15
  • @TheMaster, That is evidence that I've tried indexOf and is of little consequence to my question. As I've said in my question: " 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." Do you know why? – maxloo Jun 20 '22 at 14:19
  • @TheMaster, Do you know the meaning of parsing a string or an array? – maxloo Jun 20 '22 at 14:21
  • If `indexOf` is of no consequence, [edit] it out. You should provide a [mcve] and not your whole code. – TheMaster Jun 20 '22 at 14:23
  • `I tried to insert underscores between the letters of the string in the cells`. But that's not what your code is doing. It's a 2D array. To get to the letters, you need to go one more level deeper - another nested loop. Read my linked question... it seems you don't understand 2D arrays. Also it's preferable to add expected output you're looking for. – TheMaster Jun 20 '22 at 14:36
  • Alternatively try changing `row = row + "_" + values[i][j];` to `row = values[i][j].split("")/*level 3 array*/.join("_")`. That should `insert underscores between the letters of the string in the cells`. – TheMaster Jun 20 '22 at 14:38
  • It is unclear why you are using underscores in the first place. CSV formats usually quote values that contain commas, and escape quotes that appear in the data. See [Comma-separated values](https://en.wikipedia.org/wiki/Comma-separated_values#General_functionality) at WikiPedia. Posted an answer that shows how to do that. – doubleunary Jun 20 '22 at 15:36
  • Use [tables](https://webapps.stackexchange.com/a/161855/) to show your data structure. If you share spreadsheets, do note that [your email address can be accessed by the public](https://meta.stackoverflow.com/questions/394304/). – TheMaster Jun 21 '22 at 09:27
  • @TheMaster spreadsheets shared with the blank sheet maker tool do not reveal the poster's email address. See the second link at [The correct way to publicly share a Google test sheet](https://webapps.stackexchange.com/questions/138382/how-to-share-a-google-sheets-demo-spreadsheet/138383#138383). – doubleunary Jun 22 '22 at 17:24
  • @doubleunary Though it doesn't reveal it to the public, it still does reveal it to the form creators. I agree that it's still better than nothing. So, I'll try adding a link to that form in my canned comments in the future. – TheMaster Jun 22 '22 at 18:43
  • @TheMaster the script in the form automatically deletes email addresses. They are only used to send the user the link to the spreadsheet and are not retained. Perhaps we should continue in meta. – doubleunary Jun 23 '22 at 08:40
  • We can chat in the comments section [here](https://meta.stackoverflow.com/questions/394304/is-it-ethical-to-ask-for-a-google-sheets-file-when-answering-a-question-even-w) or in the community [chat room](https://chat.stackoverflow.com/rooms/217630/google-apps-script-chat-community) – TheMaster Jun 23 '22 at 08:43

1 Answers1

3

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')
  );
}
doubleunary
  • 13,842
  • 3
  • 18
  • 51