Thanks for taking the time to look at this - I am sure it's a simple answer. The string in the single cell in the googlesheet is simply:

1,Apple,2\n3,Orange,4

I'd like to get this value from the googlesheet and then in js convert it to the following array:

[ [1, "Apple", 2],[3, "Orange", 4] ]

The csv_to_array code below works fine when I provide it with a string in js like this (rather than from the googlesheet using getValue):

csv_to_array("1,Apple,2\n3,Orange,4");

or if I use:

strData = "1,Apple,2\n3,Orange,4";

csv_to_array(strData);

Both work.

The csv_to_array code spits out strangeness though when I try to use the string pulled from the googlesheet using getValue() in the following way:

var strData = "";

strData = ws.getRange(2, 9, 1, 1).getValue();

That gives me an output of:

[ [ 1,
    'Apple',
    '2\\n3',
    'Orange',
    4 ] ]

I have tried converting this from plain text(?) to a literal string using .String and JSON.Stringify to no effect. I have also tried defining the strData as a string"" or an array [] to see if that worked. It did not.

I have a feeling it's how the \n is being treated but have not been able to crack it. All advice/help greatly appreciated. Thanks.

const csv_to_array = (data, delimiter = ',', omitFirstRow = false) => {
    data = data.slice(omitFirstRow ? data.indexOf('\n')   1 : 0)
    let arr = data.split('\n').map(v => v.split(delimiter))
    for(let i = 0; i < arr.length; i  ){
        for(let j = 0; j < arr[i].length; j  ){
            if(!isNaN(arr[i][j])){
                arr[i][j] = parseFloat(arr[i][j])
            }
        }
    }
    return arr;
}

CodePudding user response：

Although, unfortunately, from your showing script, I'm not sure about the script of the value of That gives me an output of:, in your situation, how about the following sample scripts?

In the case of the following sample script, it supposes that your value of 1,Apple,2\n3,Orange,4 is put into a cell "A1" of "Sheet1".

Pattern 1:

In this pattern, your script csv_to_array is used.

const csv_to_array = (data, delimiter = ',', omitFirstRow = false) => {
    data = "1,Apple,2\n3,Orange,4";
    data = data.slice(omitFirstRow ? data.indexOf('\n')   1 : 0)
    let arr = data.split('\n').map(v => v.split(delimiter))
    for(let i = 0; i < arr.length; i  ){
        for(let j = 0; j < arr[i].length; j  ){
            if(!isNaN(arr[i][j])){
                arr[i][j] = parseFloat(arr[i][j])
            }
        }
    }
    return arr;
}

// Please run this function.
function main() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const value = sheet.getRange("A1").getValue();

  const array = csv_to_array(value);
  console.log(array) // [ [ 1, 'Apple', 2 ], [ 3, 'Orange', 4 ] ]
}

Pattern 2:

In this pattern, as another approach, Utilities.parseCsv is used. In this case, as an important point, \\n is replaced with \n. Because when this replacement is not done, [ [ '1', 'Apple', '2\\n3', 'Orange', '4' ] ] is obtained. I thought that this might be related to your current issue.

function main() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const value = sheet.getRange("A1").getValue().replace(/\\n/g, "\n");

  const array = Utilities.parseCsv(value, ",");
  console.log(array) // [ [ '1', 'Apple', '2' ], [ '3', 'Orange', '4' ] ]
}

Reference:

• parseCsv(csv, delimiter)