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>
>Solution :
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/>`)
)
)
);