google_sheets_api.gs

// Adjustment for reading / writing to google sheets via an embedded device (ESP32 kitchen scale)
// A part of https://hackaday.io/project/164849-yet-another-smart-kitchen-scale
// Adjusted by Uri Shani (@urishx) 2019
// Original script credentials:
// Written by: Akshaya Niraula,2016 November 12th, at http://www.embedded-lab.com/.....
// Adjustment for BME280 by Allan Schwartz at http://www.whatimade.today/log-unlimited-data-straight-to-google-sheets-from-a-bme280-temp/
// Further input from:
// https://github.com/simonbromberg/googlefitbit/blob/master/interday.gs

// HOW TO:
// 0) From Google spreadsheet, Tools > Script Editor...
// 1) Write your code
// 2) Save and give a meaningful name
// 3) Run and make sure "doGet" is selected
//    You can set a method from Run menu
// 4) When you run for the first time, it will ask 
//    for the permission. You must allow it.
//    Make sure everything is working as it should.
// 5) From Publish menu > Deploy as Web App...
//    Select a new version every time it's published
//    Type comments next to the version
//    Execute as: "Me (your email address)"
//    MUST: Select "Anyone, even anonymous" on "Who has access to this script"
//    For the first time it will give you some prompt(s), accept it.
//    You will need the given information (url) later. This doesn't change, ever!

// Saving the published URL helps for later.
// https://script.google.com/macros/s/your_google_URL/exec
// 

// This method will be called first or hits first  


// This method will be called first or hits first  
function doGet(e){  
  Logger.log("--- doGet ---");

  var getScope = "",
      majorDimension = "",
      namedRange = "",
      sheetID = "",
      startCell = "",
      endCell = "";

  try {

    // this helps during debugging
//    if (e == null){e={}; e.parameters = {getScope:"sheets"};}
    if (e == null){e={}; e.parameters = {getScope:"values", sheetID:"Sourdough_Ciabatta", startCell:"B1", endCell:"F2"};}
    if (e.parameters.getScope == undefined){e.parameters = {getScope:"values"};}

    getScope = String(e.parameters.getScope);
    
    Logger.log("GET scope:\t"+getScope);
    
   if (getScope.equals("sheets")) {
     var sheetNames = read_sheets();
     var tmpStr = "";
     for (var key in sheetNames) {
       tmpStr += key + '=' + sheetNames[key] + ';\t';
     }
     Logger.log(tmpStr);
//     Logger.log(sheetNames);
     return ContentService.createTextOutput(JSON.stringify(sheetNames)).setMimeType(ContentService.MimeType.JSON);
     
  } else if (getScope.equals("values")) {
    sheetID = e.parameters.sheetID;
    startCell = e.parameters.startCell;
    endCell = e.parameters.endCell;
    majorDimension = e.parameters.majorDimension;
    namedRange = e.parameters.namedRange;
    
    if (sheetID == undefined){sheetID = "";}
    if (startCell == undefined){startCell = "";}
    if (endCell == undefined){endCell = startCell;}
    if (majorDimension == undefined){majorDimension = "ROWS";}
    if (namedRange == undefined){namedRange = null;}
    
    Logger.log("recieved:\t"+majorDimension+",\t"+sheetID+",\t"+startCell+",\t"+endCell);
    
    var sheetValues = read_values(majorDimension, sheetID, startCell, endCell, namedRange);
    var tmpStr = "";
    
    for (var key in sheetValues) {
      tmpStr += key + '=' + sheetValues[key] + ';\t';
    } 
    
     Logger.log(tmpStr);
     Logger.log(sheetValues);
     return ContentService.createTextOutput(JSON.stringify(sheetValues)).setMimeType(ContentService.MimeType.JSON);
  } else if (getScope.equals("getPost")) {
               return getPost(e);
  } 
    
  } catch(error) { 
    Logger.log(error);    
    return ContentService.createTextOutput("oops...." + error.message 
                                           + "\n" + new Date() 
                                            + "\ntags: " + tagArray +
                                            + "\nvalues: " + valuesArray);
  }  
}

// POST response function
function getPost(e){  
  Logger.log("--- doPost ---");

  var sheetID = "",
      tagArray = new Array(),
      valuesArray = new Array();

  try {
    // this helps during debuggin
    if (e == null){e={}; e.parameters = {sheetID:"Sourdough_Ciabatta", tag1:"Flour",value1:"-1", tag2:"Sourdough", value2:"-5", tag3:"Olive_oil", value3:"-3"};}
    
    sheetID = e.parameters.sheetID;
    var eKeys = Object.keys(e.parameters);
    Logger.log(eKeys);
    
    for (var i = 0; i < eKeys.length; i++) {
      if (eKeys[i].substr(0,3) == 'tag') {
        var _tag = "e.parameters." + eKeys[i];
        var tagStr = String(eval(_tag));
        Logger.log(tagStr);
        tagArray.push(tagStr);
      } else if (eKeys[i].substr(0,5) == 'value') {
        var valStr = "e.parameters." + eKeys[i];
        Logger.log(valStr);
        valuesArray[valuesArray.length] = String(eval(valStr));
      }
      //      Logger.log(eKeys[i])
      //      Logger.log(tagArray[i]+"\t"+valuesArray[i])
    }
    
    // save the data to spreadsheet
    save_data(sheetID, tagArray, valuesArray);
    
    return ContentService.createTextOutput("Wrote recieved data to spreadsheet.\n").setMimeType(ContentService.MimeType.TEXT);

  } catch(error) { 
    Logger.log(error);    
    return ContentService.createTextOutput("oops...." + error.message 
                                           + "\n" + new Date() 
                                            + "\ntags: " + tagArray +
                                            + "\nvalues: " + valuesArray);
  }  
}

// function onOpen is called when the spreadsheet is opened; adds the Fitbit menu
function onOpen() {
  Logger.log("---onOpen start---");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ID = ss.getId();
  var ssName = ss.getName();
  var projectKey = ScriptApp.getProjectKey();
  var scriptID = ScriptApp.getScriptId();
  Logger.log("Project Key:\t"+projectKey+"\nScript ID:\t"+scriptID+"\nSpreadsheet ID:\t"+ID+"\nSpreadsheet name:\t"+ssName);
    var menuEntries = [{
                       name: "Sync",
                       functionName: "sync"
                       }, {
                       name: "Setup",
                       functionName: "setup"
                       },
                       {
                       name: "Authorize",
                       functionName: "showSidebar"
                       },
                       {
                       name: "Reset",
                       functionName: "clearService"
                       }];
    ss.addMenu("ESP32_GET_POST", menuEntries);
}

// Method to save given data to a sheet
function save_data(sheetID, tagArray, valuesArray){  
  Logger.log("--- save_data ---"); 

  try {
    var dateTime = new Date();

    // Paste the URL of the Google Sheets starting from https thru /edit
    // For e.g.: https://docs.google.com/..../edit 
    var ss = SpreadsheetApp.openByUrl("  PASTE HERE  ");
    var sheetNames, sheetNamePlaceholder = ss.getSheets();
    for (var i in sheetNames) {
      while(sheetNames[i].indexOf(' ')>-1) {
        sheetNames[i] = sheetNames[i].replace(' ','_');
        Logger.log(sheetNames[i]);
      }
    }
    var selectedSheet = ss.getActiveSheet();//getSheetByName(sheetID);
    
    for (var j in sheetNames) {
      if (sheetID === sheetNames[j]) selectedSheet = sheetNamePlaceholder[j];
    }
    
    if (sheetID === "") {
      Logger.log("entered data to first sheet in file: "+sheetNames[0].getName()); 
      selectedSheet = sheetNamePlaceholder[0];
    }
    
    // Get last edited row from DataLogger sheet
    var row = selectedSheet.getLastRow() + 1;
    
    var columnTag = selectedSheet.getRange("B1:K1").getValues();
    
    Logger.log(tagArray);
    Logger.log(valuesArray);
    selectedSheet.getRange("A" + row).setValue(dateTime); // dateTime
    
    for (var col = 0; col < columnTag[0].length; col++) {
      Logger.log("column: "+col+" value: "+columnTag[0][col]);
      for (var j = 0; j < tagArray.length; j++) {
//        Logger.log(tagArray[j]);
        var tagStr = columnTag[0][col];
        while(tagStr.indexOf(' ')>-1) {
          tagStr = tagStr.replace(' ','_');
          Logger.log(tagStr);
        }
        if (tagArray[j].equals(tagStr) == true) {
          Logger.log("row: "+row+" column: "+col+" j: "+j);
          selectedSheet.getRange(row, col + 2).setValue(valuesArray[j]); // value
        }
      }
    }
  }

  catch(error) {
    Logger.log(JSON.stringify(error));
  }

  Logger.log("--- save_data end---"); 
}

// method to read and return JSON of sheet names in spreadsheet
function read_sheets() {
 Logger.log("---read_sheets start---") ;
  
  try {
    // Paste the URL of the Google Sheets starting from https thru /edit
    // For e.g.: https://docs.google.com/..../edit 
    var ss = SpreadsheetApp.openByUrl("  PASTE HERE  ");
    var sheets = ss.getSheets();
    var sheetNames = new Array();
    var sheetJSON = {};
    
    for (var k in sheets) {
      var sheetName = sheets[k].getName();
      Logger.log(sheetName);
      while(sheetName.indexOf(' ')>-1) {
        sheetName = sheetName.replace(' ','_');
        Logger.log(sheetName);
      }
      sheetNames.push(sheetName);
    }
    
    sheetNames.forEach(function (value, index){
      sheetJSON['Sheet' + (index + 1)] = value;
    });
        
    Logger.log(sheetNames);
    return sheetJSON;
  }
  
  catch(error) {
    Logger.log(JSON.stringify(error));
  }

  Logger.log("---read_sheets end---"); // program will not get here unless there's an error
}

// method to read and return JSON of values in specified cells inside selected sheet
function read_values(majorDimension, sheetID, startCell, endCell, namedRange) {
 Logger.log("---read_values start---") ;
  
  try {
    // Paste the URL of the Google Sheets starting from https thru /edit
    // For e.g.: https://docs.google.com/..../edit 
    var ss = SpreadsheetApp.openByUrl("  PASTE HERE  ");
    var range;
    var values = new Array();
    var sheets = ss.getSheets();
    var sheetNames = new Array();
    for (var i in sheets) {
      var sheetName = sheets[i].getName();
      Logger.log(sheetName);
      while(sheetName.indexOf(' ')>-1) {
        sheetName = sheetName.replace(' ','_');
        Logger.log(sheetName);
      }
      sheetNames.push(sheetName);
    }
    var selectedSheet = ss.getActiveSheet();//getSheetByName(sheetID);
    
    for (var j in sheetNames) {
      if (String(sheetID) === sheetNames[j]) selectedSheet = sheets[j].activate();
    }
    
    if (String(sheetID) === "") {
      Logger.log("read data from first sheet in file: "+sheetNames[0]); 
      selectedSheet = sheets[0].activate();
    } else if (String(sheetID) === "CURRENT_SHEET") {
      selectedSheet = ss.getActiveSheet();
    }
    
    if (namedRange == null) {
      if (String(startCell) === "") {
        startCell = "A1";
        if (String(endCell) === "") {endCell = startCell;}
      } else if (String(startCell) === "CURRENT_CELL") {
        range = selectedSheet.getCurrentCell().getA1Notation();
        Logger.log(startCell+"\t"+range);
      }
      Logger.log("1st cell:\t"+startCell+",\tlast cell:\t"+endCell);
      
      if (range == undefined) {
        range = startCell + ":" + endCell;
      }
    } else {
      // The code below logs the name of the first named range.
//var namedRanges = SpreadsheetApp.getActiveSheet().getNamedRanges();
//if (namedRanges.length > 1) {
//  Logger.log(namedRanges[0].getName());
//}
      
      var namedRangesInSheetList = new Array();
      namedRangesInSheetList = SpreadsheetApp.getActiveSheet().getNamedRanges();
      Logger.log(namedRangesInSheetList);
//      var namedRangesInSheetNames = namedRangesInSheetList;
      
      namedRangesInSheetList.forEach(function (value, index){
        if (String(namedRange) === value.getName()){range = String(value);}
      });
      
      Logger.log(range);
    }
            
    Logger.log("cell range:\t"+range);
    
    if (String(majorDimension) === "COLUMNS") {
      var tmpVals = selectedSheet.getRange(range).getValues();    
      for (var col = 0; col < tmpVals[0].length; col++) {
        var columnValues = new Array();
        for (var row = 0; row < tmpVals.length; row++) {
          columnValues.push(tmpVals[row][col]);
          Logger.log(columnValues);
        }
        values.push(columnValues);
      }
    } else {
      var type = selectedSheet.getRange(range).getNumberFormats();
      values = selectedSheet.getRange(range).getDisplayValues();
    }    
    
    for (var m in values) {
      for (var n in values[m]) {
        var valueString = values[m][n];
        while(valueString.indexOf(' ')>-1) {
          valueString = valueString.replace(' ','_');
          Logger.log(valueString);
          values[m][n] = valueString;
        }
      }
    }
          
    var valuesJSON = {};
    
    valuesJSON['range'] = sheetNames[selectedSheet.getSheetId()] + "!" + range;
    valuesJSON['majorDimension'] = String(majorDimension);
    valuesJSON['values'] = values;
    
    Logger.log(values);
    return valuesJSON;
  }
  
  catch(error) {
    Logger.log(JSON.stringify(error));
  }

  Logger.log("---read_values end---"); // program will not get here unless there's an error
}