Getting Error ( range.getDisplayValues is not a function ) in App Script-CodePudding

Here my Goal is to display a google sheet range as a table in my site.

The default range for rangeB to be displayed in table is "P6:R34" ;

But when times up it should be "P5:R34"

So I wrote my code But it's giving below error.

TypeError: range.getDisplayValues is not a function (line 31, file "Code")

What went wrong (or) how to combine these ranges in App Script ..?

Code.gs

function doGet() {
  template = HtmlService.createTemplateFromFile('Index.html');
  return template.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

var now = new Date().getTime() ;

var d = new Date();
var year = d.getFullYear();
var month = d.getMonth();
var date = d.getDate();
var ctdr = new Date(year, month, date, 14, 13, 00).getTime();

distance = ctdr - now;

var spreadsheetId   = '1jH0y-PknkZXH7KqjPBWDv98kkBnndGt_GIbdUh_1nRM';

var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("LNW") ;
  
rangeA = sheet.getRange("P4:R4") ;

rangeB = sheet.getRange("P6:R34") ;

range = [rangeA, rangeB] ;

if (distance < 0) {
rangeB = sheet.getRange("P5:R34") ;
}

var dataValues = range.getDisplayValues()
    .map(row => row.map(value =>
          value.replace(/\n/g, `<br/>`)
        )
     );

Index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

  <style>
    table, tbody, tr, th, td {
      text-align:center;
      font-family: Arial, sans-serif;
      font-size: 14.5px ;
      border-collapse: collapse;
      margin : auto ;
    }

    th {
      background-color: #f5f5f5 ;
      padding : 6px ;
    }
  </style>

  <body>

    <table cellpadding="12px" >
      <? var tableData = dataValues ?>
      <? for(var i = 0; i < tableData.length; i  ) { ?>
      <? if(i == 0) { ?>
        <tr>
        <? for(var j = 0; j < tableData[i].length; j  ) { ?>
        <th> <?!= tableData[i][j] ?></th>
        <? } ?>
        </tr>
      <? } else { ?>
        <tr>
        <? for(var j = 0; j < tableData[i].length; j  ) { ?>
        <td> <?!= tableData[i][j] ?></td>
        <? } ?>
        </tr>
      <? } ?>
      <? } ?>
    </table>
  </body>
</html>

CodePudding user response：

range is an array of Range.

Besides, you modified rangeB after range is defined which I think it is not what you intend.

rangeA = sheet.getRange("P4:R4") ;

rangeB = sheet.getRange("P6:R34") ;

if (distance < 0) {
rangeB = sheet.getRange("P5:R34") ;
}

ranges = [rangeA, rangeB] ;

var dataValues = ranges.flatMap(range =>
  range.getDisplayValues()
    .map(row => row.map(value =>
          value.replace(/\n/g, `<br/>`)
        )
     )
  );

CodePudding user response：

Your range variable seems to be an array, that does not have access to the getDisplayValues function.

range = [rangeA, rangeB] ; 
var dataValues = range.getDisplayValues()
.map(row => row.map(value =>
      value.replace(/\n/g, `<br/>`)
    )
 );

You should move the getDisplayValues to the actual range object (rangeA, rangeB).

it should look like:

var ranges = [rangeA,rangeB];
var dataValues = ranges.map(function(range){
  range.displayValue.map().....etc.
});