I need to generate a table in Google Docs from a set of value pairs in a single spreadsheet cell. The values in the cell (A1) are formatted like this:

 ["a1","a2"],["b1","b2"],["c1","c2"]

I've tried:

testData = testSheet.getRange(1,1,1, 1).getValues().toString().split(",");
Logger.log(testData)

This gives me:

[["a1","a2"],["b1","b2"],["c1","c2"]]

Which looks like a 2D array to me, but when I try:

testDoc.insertTable(testField, testData)

I get the error:

Exception: The parameters (number,number[]) don't match the method signature for DocumentApp.Body.insertTable.

When I define the array manually in the function with:

testData = [["a1","a2"],["b1","b2"],["c1","c2"]]

The table is inserted as expected.

I could format the data in the cell differently, but I need all the value pairs to be in a single cell.

CodePudding user response：

Modification points:

• When ["a1","a2"],["b1","b2"],["c1","c2"] is put in a cell "A1", and when the cell value is retrieved by testSheet.getRange(1,1,1, 1).getValues(), it is [["[\"a1\",\"a2\"],[\"b1\",\"b2\"],[\"c1\",\"c2\"]"]]. In this case, [\"a1\",\"a2\"],[\"b1\",\"b2\"],[\"c1\",\"c2\"] is the string type.

  • This can be confirmed by JSON.stringify.

• By this, when the cell value is retrieved by testSheet.getRange(1, 1, 1, 1).getValues().toString().split(","), it becomes ["[\"a1\"","\"a2\"]","[\"b1\"","\"b2\"]","[\"c1\"","\"c2\"]"]. In this case, [\"a1\"","\"a2\"], [\"b1\"","\"b2\"] and [\"c1\"","\"c2\"] are the string values.

  • I thought that this is the reason for your issue.

• In order to use the cell value of ["a1","a2"],["b1","b2"],["c1","c2"] as a 2 dimensional array, it is required to parse it.

When these points are reflected in your script, how about the following modification?

From:

testData = testSheet.getRange(1,1,1, 1).getValues().toString().split(",");

To:

testData = JSON.parse(`[${testSheet.getRange(1, 1, 1, 1).getValue()}]`); // In this case, I think that `getDisplayValue()` is used instead of `getValues`.

• By this modification, the value of ["a1","a2"],["b1","b2"],["c1","c2"] can be used as 2 dimensional array of [["a1","a2"],["b1","b2"],["c1","c2"]].

Testing:

const value = '["a1","a2"],["b1","b2"],["c1","c2"]';
const testData = JSON.parse(`[${value}]`);
console.log(testData)
console.log(testData[0][0]) // a1

Reference:

• JSON.parse()

CodePudding user response：

I'd try this:

var testData = eval('['   testSheet.getRange('A1').getDisplayValue()   ']');

Test code:

var s =  '["a1","a2"],["b1","b2"],["c1","c2"]';

var testData = eval('['   s   ']');

console.log(testData);