freqqie.gs

SPREADSHEET_URL = "SPREADSHEET_URL"

CHART_WIDTH = 1200; // width of each chart in pixel
CHART_HEIGHT = 600; // height of each chart in pixel
CHARTS_PER_ROW = 2; // number of charts in a row

function doGet(e) {
  doPost(null);
}

function doPost(e) {
  if (!e) {
    e = {"parameters":
         {"events":
          '[{"tag": "test","time": "1","record": {"freq": 79368750.0, "power_db": 10.0}},{"tag": "test","time": "2","record": {"freq": 79368760.0, "power_db": 15.0}},{"tag": "test","time": "2","record": {"freq": 79368770.0, "power_db": 20.0}},{"tag": "test","time": "2","record": {"freq": 79368760.0, "power_db": 3.0}}]'
      }
    }
  }
  
  // process each event
  var events = JSON.parse(e.parameters.events);
  for each (var event in events) {
    processEvent(event);
  }

  // response with empty string
  return ContentService.createTextOutput("ok");
}

function processEvent(event) {
  // extract fluentd tag and timestamp
  if (!event.record) return;
  var tag = event.tag;
  delete event.tag;
  var timestamp = new Date(Number(event.time) * 1000);
  delete event.time;
  
  var freq = (event.record.freq);
  var power_db = (event.record.power_db);
  
  var tableSheetName = tag;
  var sheets = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  updateSheet(sheets, tag, freq, power_db);
  
  updateChart(sheets, tag);
  
  return;
}

function updateSheet(sheets, tag, freq, power_db) {
  var tableSheet = sheets.getSheetByName(tag);
  if (!tableSheet) {
    tableSheet = sheets.insertSheet(tag);
  }

  var lastidx = tableSheet.getLastRow();
  
  Logger.log("NEW: event => freq: %s, power_db: %s", freq, power_db);
  Logger.log("SHEET: last row idx => %s", lastidx);
  
  if (lastidx == null || lastidx == 0.0) {
    /* very first */
    Logger.log("ADD: head frequency %s (> last %s)", freq, lastfreq);
    tableSheet.getRange(1, 1).setValue("Frequency[Hz]");
    tableSheet.getRange(1, 2).setValue("Power[DB]")
    tableSheet.getRange(2, 1).setValue(freq);
    tableSheet.getRange(2, 2).setValue(power_db);
    return;
  }
  
  var lastfreq = tableSheet.getRange(lastidx, 1).getValue();
  Logger.log("SHEET: last row freq => %s", lastfreq);
  
  if (lastfreq < freq) {
    Logger.log("ADD: new frequency %s (> last %s)", freq, lastfreq);
    tableSheet.getRange(lastidx + 1, 1).setValue(freq);
    tableSheet.getRange(lastidx + 1, 2).setValue(power_db);
    return;
  }
  
  Logger.log("SHEET: update");
  
  /* update */
  var idx = 0;
  for (idx = 2 ; idx <= lastidx; idx++) {
    _freq = tableSheet.getRange(idx, 1).getValue();
    Logger.log("UPDATE: idx=%s : freq %s vs _freq %s (%s vs %s)", idx, freq, _freq, typeof freq, typeof _freq);
    if (_freq == freq) {
      Logger.log("UPDATE: idx=%s : found", idx);
      break;
    }
  }
  
  if (idx ==0 || idx > lastidx) {
    Logger.log("UPDATE: freq %s not found: exceed limit", freq);
    return;
  }
  
  tableSheet.getRange(idx, 2).setValue(power_db);
  
  return;
}

function updateChart(sheets, tag) {
  Logger.log("CHART: add chart for %s", tag);
  var firstSheet = sheets.getSheets()[0];
  var numCharts = firstSheet.getCharts().length;
  var theChart = firstSheet.getCharts()[0];
  
  var posX = (numCharts % CHARTS_PER_ROW) * CHART_WIDTH;
  var posY = Math.floor(numCharts / CHARTS_PER_ROW) * CHART_HEIGHT;
  
  var tableSheet = sheets.getSheetByName(tag);
  if (!tableSheet) {
    return;
  }
  
  Logger.log("CHART: range = (%s, %s, %s, %s)", 0, 0, tableSheet.getLastRow(), tableSheet.getLastColumn());
  
  if (!theChart) {
    var chart = firstSheet.newChart()
    .asScatterChart()
    .addRange(tableSheet.getRange(1, 1, tableSheet.getLastRow(), 2))
    //.addRange(tableSheet.getRange("A:B"))
    .setPosition(1, 1, posX, posY)
    .setOption("width", CHART_WIDTH)
    .setOption("height", CHART_HEIGHT)
    .setOption("isStacked", false)
    .setOption("title", tag)
    .build();
    firstSheet.insertChart(chart);
    theChart = chart;
  }
  theChart = theChart.modify().addRange(tableSheet.getRange(1, 1, tableSheet.getLastRow(), 2)).build();
  //firstSheet.updateChart(theChart);
  
  return;
  
}