Create dropdown from JSON data on HTML page using Google Apps Script

Question

I am trying to create a dropdown from JSON data received from Google Sheet. I have code which gets data from Google Sheets to JSON. I am not able to create dropdown. This dropdowns I want to use for creating a graph. Here is a code which gets sheet data into json.

    function doGet(e){

     // Change Spread Sheet url
     var ss = SpreadsheetApp.openByUrl("URL to the Sheet");

    // Sheet Name.
     var sheet = ss.getSheetByName("waiverstatus");

     return getUsers(sheet); 
    }


    function getUsers(sheet){
      var jo = {};
      var dataArray = [];

    // collecting data from 2nd Row , 1st column to last row and last column
      var rows = sheet.getRange(2,1,sheet.getLastRow()-1,                   sheet.getLastColumn()).getValues();

      for(var i = 0, l= rows.length; i<l ; i++){
        var dataRow = rows[i];
        var record = {};
        record['Students'] = dataRow[0];
        record['Method'] = dataRow[1];
        record['Satisfactory_C1'] = dataRow[2];
        record['Developing_C1'] = dataRow[3];
        record['Unsatisfactory_C1'] = dataRow[4];
        record['Satisfactory_C2'] = dataRow[5];
        record['Developing_C2'] = dataRow[6];
        record['Unsatisfactory_C2'] = dataRow[7];


        dataArray.push(record);

      }  

      jo.user = dataArray;

      var result = JSON.stringify(jo);

      return         ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

    }

Everything should be in "jo", where is your client HTML that parses jo and builds a dropdown list? — TheWizEd
– TheWizEd, Commented Mar 26, 2019 at 19:45
Just a very basic code hygiene tip here: the name of your variables should actually communicate their purpose. You created a variable named 'ss' and wrote a comment on top of it; also created the variable 'sheet' and added a comment on top of it; Why not just rename 'ss' to spreadsheetUrl and name sheet as sheetName so you don't need a comment to remind what they're there for? Same with the variable 'jo' - I can't understand what it's doing just by reading it's name. — leosteffen
– leosteffen, Commented Mar 26, 2019 at 22:39

TheWizEd · Accepted Answer · 2019-03-26 22:32:56Z

2

Here is an example of making a dropdown from a spreadsheet.

Code.gs

function test() {
  try {
    var html = HtmlService.createTemplateFromFile("HTML_GetJo").evaluate();
    SpreadsheetApp.getUi().showModalDialog(html, "Jo");
  }
  catch(err) {
    Logger.log(err);
  }
}

function get_jo() {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var rows = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
    var jo = {};
    var dataArray = [];
    for( var i=0; i<rows.length; i++ ) {
      var record = {};
      record['Students'] = rows[i][0];
      record['Method'] = rows[i][1];
      record['Satisfactory_C1'] = rows[i][2];
      record['Developing_C1'] = rows[i][3];
      record['Unsatisfactory_C1'] = rows[i][4];
      record['Satisfactory_C2'] = rows[i][5];
      record['Developing_C2'] = rows[i][6];
      record['Unsatisfactory_C2'] = rows[i][7];
      dataArray.push(record);
    }
    jo.user = dataArray;
    var result = JSON.stringify(jo);
    return result;
  }
  catch(err) {
    Logger.log(err);
  }
}

HTML_GetJo.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select id="myjo">
    </select>
    <script>
      (function () {
        google.script.run.withSuccessHandler(
          function(value) {
            var jo = JSON.parse(value);
            var select = document.getElementById("myjo");
            for( var i=0; i<jo.user.length; i++ ) {
              var user = jo.user[i];
              var option = document.createElement("option");
              option.text = user.Students;
              select.add(option);
            }
          }
        ).get_jo();
      }());
    </script>
  </body>
</html>

answered Mar 26, 2019 at 22:32

TheWizEd

8,6542 gold badges14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alpesh Ahir Over a year ago

Exception: Cannot call SpreadsheetApp.getUi() from this context.

TheWizEd Over a year ago

Your question was how to create a drop down which I have shown in the html file and get_jo function. How you execute this is up to you. I'm assuming you are using a web app because of doGet. My example uses a bound script.

Collectives™ on Stack Overflow

Create dropdown from JSON data on HTML page using Google Apps Script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related