Hi. Anyone can show me how to get line graph that was update live on mit app. Then , the data was extract from google sheet . but i don know how and what blocks needed . Thankyou.
How often do you need to update the graph?
An example using google charts:
<!DOCTYPE html>
<html>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
<head>
<title>Live Data Line Chart</title>
</head>
<body>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div" style="width:600px; height:400px"></div>
<script>
google.charts.load('current', {packages: ['corechart', 'line']});
google.charts.setOnLoadCallback(drawBasic);
function drawBasic() {
var data = new google.visualization.DataTable();
data.addColumn('number', 'X');
data.addColumn('number', 'Y');
data.addRows([
[0, 0], [1, 10], [2, 23], [3, 17], [4, 18], [5, 9],
[6, 11], [7, 27], [8, 33], [9, 40], [10, 32], [11, 35],
[12, 30], [13, 40], [14, 42], [15, 47], [16, 44], [17, 48],
[18, 52], [19, 54], [20, 42], [21, 55], [22, 56], [23, 57],
[24, 60], [25, 50], [26, 52], [27, 51], [28, 49], [29, 53]
]);
let num = data.getNumberOfRows();
var options = {
legend: {position: 'none'},
hAxis: {
title: 'Time',
viewWindow: {
min: num - 28,
max: num
},
},
vAxis: {
title: 'Popularity'
}
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
setInterval(function() {
let num = data.getNumberOfRows();
data.addRow([num, 40 + Math.round(60 * Math.random())]);
var options = {
legend: {position: 'none'},
hAxis: {
title: 'Time',
viewWindow: {
min: num - 28,
max: num
},
},
vAxis: {
title: 'Popularity'
}
};
chart.draw(data, options);
}, 2000);
}
</script>
</body>
</html>
You can use the webviewstring to feed the chart with new data.
Hi, i already get the solution. The after the coding at appscript , there is need to set up at triger on app script . From there i can set up timedriven , either in minute, hours or specific time.
in every minute. I already found the coding and set up from trigger.
Perhaps you would like to share your solution so that others may learn....
1 Like
The coding that need to upload at Appscript was here .
// Specify the sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Specify the target columns
var targetColumns = ['I', 'J', 'K', 'L'];
// Specify the range of rows where you want to display the value (e.g., rows 2 to 100000)
var startRow = 2;
var endRow = 100000;
var totalRows = endRow - startRow + 1;
// Function to send data to the sheet and display continuous values
function sendDataAndDisplayContinuous() {
sendDataToSheet();
displayContinuousValue();
}
// Fungsi untuk menampilkan nilai yang sama secara terus-menerus di baris target yang berbeda
function displayContinuousValue() {
// Ganti 'Your Values' dengan nilai yang ingin Anda tampilkan
var valuesToDisplay = ['0.5', '8', '10', '15'];
// Menampilkan nilai di sel target yang ditentukan untuk setiap baris secara berulang
var currentRow = getCurrentRow();
for (var i = 0; i < targetColumns.length; i++) {
var targetCell = sheet.getRange(currentRow, sheet.getRange(targetColumns[i] + 1).getColumn());
targetCell.setValue(valuesToDisplay[i]);
}
}
// Fungsi untuk mendapatkan baris saat ini berdasarkan jumlah total baris
function getCurrentRow() {
var properties = PropertiesService.getScriptProperties();
var currentRow = parseInt(properties.getProperty('currentRow')) || startRow;
// Increment currentRow and store it in script properties
currentRow = (currentRow % totalRows) + 1;
properties.setProperty('currentRow', currentRow);
return currentRow;
}
// @ts-nocheck
//'2185447';'RMG5KWLNH3731MMS';
function sendDataToSheet() {
// Replace 'YOUR_CHANNEL_ID' and 'YOUR_API_KEY' with your own Thingspeak channel ID and API key
var channelId = '2185447';
var apiKey = 'RMG5KWLNH3731MMS';
// Replace 'SHEET_NAME' with the name of the sheet you want to send the data to
var sheetName = 'Sheet2';
var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
var lastEmailTimestamp = 0;
// Get the last entry in the Thingspeak channel
var response = UrlFetchApp.fetch('https://api.thingspeak.com/channels/' + channelId + '/feeds/last.json?api_key=' + apiKey, {muteHttpExceptions: true});
var data = response.getContentText();
var dataJson = JSON.parse(data);
//get the date and time (current)
var now = new Date();
var date = Utilities.formatDate(now, "GMT+0000", "dd-MM-yyyy");
var time = Utilities.formatDate(now, "GMT+0000", "HH:mm:ss");
// Get the field values from the response, uncomment for specific field/s
var field1 = dataJson.field1; //Rain Intensity on Device A
var field3 = dataJson.field3; //Water Level (Height from Lower Pipe) on Device A
var field4 = dataJson.field4; //Possible flood on B
var field5 = dataJson.field5; //Rain Intensity on Device B
var field7 = dataJson.field7; //Water Level (Height from Lower Pipe) on Device B
// Append the data to the sheet
sheet.appendRow([now, date, time, field1, field3, field4, field5, field7]);
}
1 Like