Copying Google Sheets table to Google Document - Error with Boolean cells

Viewed 55

I am very new to programming google app script, just trying to make some custom function. Sorry if this question is too basic...

I was following a tutorial to copy a table from Google Sheets into Google document but the exact same code the instructor was using did not worked for me.

I was getting this error:

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

The simplified version of the code is:

function fun4(){
  var ss = SpreadsheetApp.openById('17-23aFf6mN5oQrKNwNDy3Zh24_foTN5mXzNkjvd3V5w');
  var sheet = ss.getSheets()[0];

  var doc = DocumentApp.create('Sample Sheet Data');
  var body = doc.getBody();

  var numLines =  sheet.getLastRow();
  var numColumns = sheet.getLastColumn();

  var rowData = sheet.getRange(1, 1, numLines, numColumns).getValues();
  console.log(rowData);

  var table = body.appendTable(rowData);   // ERROR IN THIS LINE
  table.getRow(0).editAsText().setBold(true);
}

After some search I found that the problem was caused by the last column containing Boolean values and changed .getValues() to .getDisplayValues().

It is working now but I am very confused... How it was working in the instructor code but not in mine? Why did not work if the output looks to be in the same format (double array?)

Code: (Gives an error when I append the table to doc , but worked in the instructor video)

  var rowData = sheet.getRange(1, 1, numLines, numColumns).getValues();
  console.log(rowData);

Output:

11:36:05 AM Info    
[ [ 'NAME', 'EMAIL', 'AGE', 'ACTIVE' ],
  [ 'Alex', 'alex@gmail.com', 50, true ],
  [ 'Brian', 'brian@gmail.com', 34, false ],
  [ 'Julian', 'julian@gmail.com', 42, true ],
  [ 'John', 'john@gmail.com', 24, false ] ]

Code:

  var rowData = sheet.getRange(1, 1, numLines, numColumns).getDisplayValues();
  console.log(rowData);

Output:

11:36:05 AM Info    
[ [ 'NAME', 'EMAIL', 'AGE', 'ACTIVE' ],
  [ 'Alex', 'alex@gmail.com', '50', 'TRUE' ],
  [ 'Brian', 'brian@gmail.com', '34', 'FALSE' ],
  [ 'Julian', 'julian@gmail.com', '42', 'TRUE' ],
  [ 'John', 'john@gmail.com', '24', 'FALSE' ] ]
1 Answers

I believe your goal as follows.

  • You want to know the reason the following situation.

    After some search I found that the problem was caused by the last column containing Boolean values and changed .getValues() to .getDisplayValues(). It is working now but I am very confused... How it was working in the instructor code but not in mine? Why did not work if the output looks to be in the same format (double array?)

Answer:

About the error at the script of var rowData = sheet.getRange(1, 1, numLines, numColumns).getValues();, I thought that the reason of the issue might be due to using V8 runtime. Ref

  • When V8 runtime is enabled at the script editor, I confirmed that the error of The parameters (number[]) don't match the method signature for DocumentApp.Body.appendTable. occurred.
  • When V8 runtime is disabled at the script editor, I confirmed that no error occurred.
    • The table can be created using the value of var rowData = sheet.getRange(1, 1, numLines, numColumns).getValues(); and your sample values.

From your question, unfortunately, I cannot understand about the instructor video of Code: (Gives an error when I append the table to doc , but worked in the instructor video). But, above situation, I guess that the instructor video might not use V8 runtime.

So, in your script, when you want to test whether var rowData = sheet.getRange(1, 1, numLines, numColumns).getValues(); works, how about disabling V8 runtime at the script editor as follows and testing it again?

enter image description here

Reference:

Related