How can I display my live sensor data in the google sheets in only "one cell"?

user_13705477 0
Viewed 81

Basically, I made a weighing machine using m5stack that is connected to the internet. I am importing my live data to the Google spreadsheets but, I don't want that much data. I only want to display that live data in a single cell. (like a meter?)

I tried to write a code in an app script for the same but Find out difficult.

Here is my code for importing my data to the spreadsheets:

function doPost(e) {
  // シート取得
  var spreadsheet = SpreadsheetApp.openById("***"),
  sheet = spreadsheet.getSheets()[0];
      
  var postjsonString = e.postData.getDataAsString();
  var postdata = JSON.parse(postjsonString);
 
  date = new Date();
  date_time =  Utilities.formatDate(date, 'JST', 'yyyy年M月d日 H時m分s秒'),
  sheet.appendRow([date_time,postdata.sensor.a]); 
 
}
google-apps-script google-sheets javascript
1 Answers

I believe your current situation and your goal as follows.

  • Your value of postdata.sensor.a is correct value you expect.
  • You want to put the value of postdata.sensor.a to a cell and you want to create a chart of gauge type (this is from like a meter?).

Modification points:

  • From your script, I would like to propose to use 2 sheets. One is a log sheet which is append the values of sheet.appendRow([date_time,postdata.sensor.a]); as a log. Another is a chart sheet which shows a gauge chart using the current value.

When above points are reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please set your Spreadsheet ID and the maximum and minimum values for the gauge chart you expect.

function doPost(e) {
  var postjsonString = e.postData.getDataAsString();
  var postdata = JSON.parse(postjsonString);

  // I modified below script.
  var spreadsheet = SpreadsheetApp.openById("###");
  var logSheet = spreadsheet.getSheetByName("logSheet") || spreadsheet.insertSheet("logSheet");
  var chartSheet = spreadsheet.getSheetByName("chartSheet") || spreadsheet.insertSheet("chartSheet");

  // Put the values for the log.
  var date_time =  Utilities.formatDate(new Date(), 'JST', 'yyyy年M月d日 H時m分s秒');
  var values = [date_time, postdata.sensor.a];
  logSheet.appendRow(values);

  // Put the values for the chart.
  chartSheet.getRange("A1:B1").setValues([values]);

  // Create gauge chart if the chart is not existing in the chart sheet.
  var max = 100;  // <--- Please set the maximum value for your actual situation.
  var min = 0;  // <--- Please set the minumum value for your actual situation.
  var charts = chartSheet.getCharts();
  if (charts.length == 0) {
    var chart = chartSheet.newChart().setChartType(Charts.ChartType.GAUGE).addRange(chartSheet.getRange('B1')).setPosition(3, 1, 0, 0).setOption('height', 300).setOption('width', 300).setOption('title', 'Sample gauge').setOption('max', max).setOption('min', min).build();
    chartSheet.insertChart(chart);
  }
}
  • In this modified script, when doPost is run, the values of date_time, postdata.sensor.a are appended to the sheet of "logSheet". And also, the values are put to the cells "A1:B1" in the sheet of "chartSheet". When those sheets are not existing, the sheets are automatically inserted.
  • At "chartSheet", when the chart is not existing, the chart of gauge type is created using the put value.

Result:

When above script is used for the test values, the following result in "chartSheet" is obtain.

enter image description here

Note:

  • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.
  • This is a simple modified script. So please modify this for your actual situation. For example, if you don't want to use "logSheet", please remove the script for it.

References:

user_71086530
Related