Why not just var test = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getRange(3, 7, rows.getLastRow()-2, 1).getValues(); The third param is number of rows not last row
Solution 1 :
Solution 2 :
Arrays weren’t being passed as arrays because I had a function inside them. They were all being passed as undefined. I had to change a bit my sheet but it works now. Also null was showing as BarLabel so I turned BarLabel off with options. It works great now!
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['timeline']});
google.charts.setOnLoadCallback(getData);
function getData(){
google.script.run.withSuccessHandler(drawChart).useDataRange();
}
function drawChart(data) {
var container = document.getElementById('timeline-tooltip');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();
dataTable.addColumn({ type: 'string', id: 'President'});
dataTable.addColumn({ type: 'string', id: 'dummy bar label'});
dataTable.addColumn({ type: 'string', role: 'tooltip'});
dataTable.addColumn({ type: 'string', id: 'style', role: 'style'});
dataTable.addColumn({ type: 'date', id: 'Start'});
dataTable.addColumn({ type: 'date', id: 'End'});
var rows = [];
//Loop through the data you loaded and push it to the rows array
for(var i=0; i<data.length; i++){
var currentElement = data[i];
var transformFirstDate = new Date(currentElement[4]);
var transformSecondDate = new Date(currentElement[5]);
rows.push([currentElement[0],currentElement[1],currentElement[2],currentElement[3],transformFirstDate,transformSecondDate]);
}
dataTable.addRows(rows);
var options = {
timeline: { showBarLabels: false }
};
chart.draw(dataTable, options);
}
</script>
</head>
<body>
<!--Div that will hold the pie chart-->
<div id="timeline-tooltip" style="height: 400px;"></div>
</body>
</html>
Problem :
I’m building a web app that is google’s chart Timeline. I have a script that returns me the correct array from google sheets. I want to pass this result to HTML in a specific place. I’m using google app scripts.
function doGet(e) {
return HtmlService.createTemplateFromFile('timeline3').evaluate();
}
function useDataRange() {
var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
var test = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getRange(3, 7, rows.getLastRow(), 1).getValues();
test = test.slice(0,rows.getLastRow()-2);
JSON.stringify(test);
Logger.log(test);
return test;
}
Here is my HTML code:
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<body>
<script type="text/javascript">
google.charts.load('current', {'packages':['timeline']});
google.charts.setOnLoadCallback(getData);
function getData(){
return google.script.run.withSuccessHandler(drawChart).useDataRange();
}
function drawChart(arrayFromSheets) {
var container = document.getElementById('timeline-tooltip');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();
dataTable.addColumn({ type: 'string', id: 'President' });
dataTable.addColumn({ type: 'string', id: 'dummy bar label' });
dataTable.addColumn({ type: 'string', role: 'tooltip' });
dataTable.addColumn({ type: 'string', id: 'style', role: 'style' });
dataTable.addColumn({ type: 'date', id: 'Start' });
dataTable.addColumn({ type: 'date', id: 'End' });
dataTable.addRows(arrayFromSheets);
chart.draw(dataTable);
}
</script>
<div id="timeline-tooltip" style="height: 400px;"></div>
</body>
</html>
I’m trying to populate dataTable.addRows() with my array. I’ve looked through google’s documentation and this should work. I just don’t get it. Please help
Here is an example of array I get returned from useDataRange function:
[['Lady Gita' ,null , 'June 19 -26' , '#176BEF', new Date(2021, 6, 19) , new Date(2021, 6, 26)], ['Lady Gita' ,null , 'Jun 27 - Jul 11' , '#FF3E30', new Date(2021, 6, 27) , new Date(2021, 7, 11)]]
Searching in console I get this:
Console:
Comments
Comment posted by SputnikDrunk2
I’ve replicated your script and the issue could be due to the date in the array. The date in the array when passed will be in this format: “Thu Apr 30 00:00:00 GMT-15:56 1789”. This is just purely based on my observations, but it looks like the dataTable will fail to identify the date on the array, thus the chart creation. All sample articles/resources from Google Charts only show the use of JavaScript Date class like “new Date(1789, 3, 30)” when adding a date value on the dataTable & it looks this is not possible (at least to Google Chart) given there are no existing guide online.
Comment posted by developers.google.com/chart/interactive/docs/…
An alternative option that you can try is the guide from “Creating a Chart from a Separate Spreadsheet” at
Comment posted by WhiteHat
yes, use the Query class as noted above…
Comment posted by Vice Radica
Hey Irvin and WhiteHate, thank you for you answers. I’ll update you when I do it 🙂
Comment posted by Vice Radica
Hey, I really suck at querries, have to take time to learn them. I updated my answer with working solution
Comment posted by Vice Radica
Hey Cooper. Thank you for your answer. I’m not having a problem with getting the correct array. My logger clearly shows the correct array and if I just paste it in HTML it works. I want to pass this array to HTML code in addRows();.
Comment posted by Cooper
I assumed the html has already been rendered so you will have to request the array and have it returned to a withSuccessHandler
Comment posted by Vice Radica
Hey Cooper, thanks for the answer! I thought I already did request the array and return it (see function getData()). I opened console and it gives me this: jsapi_compiled_default_module.js:295 Uncaught Error: Argument given to addRows must be either a number or an array So it was not clear to me what happens when getData is executed so I put after that function closes alert(getData()); and it alerts: undefined