How to get info from google sheets based on drop list selection?

Question

In my webapp show a drop list that get the sheets name from a google sheets file so I'm trying to get the sheet info based on the selected sheet from the drop list . the code works but not based on the selection of droplist. what I did wrong ? here is the full sample hopefully it will help to identify the issue

CODE.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('dup');
}

function getSheetNames() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  var sObj={sA:[]};
  shts.forEach(function(sh){
    sObj.sA.push(sh.getName());
  })
  return sObj;
}


function getDataFromServer(e) {
  var ss=SpreadsheetApp.getActive();
  var data =ss.getSheetByName(e.name).getRange("B2:J22").getValues();
  var ar = data.splice(0,1); //add headers
  data.forEach(function(f) {
    if (~f.indexOf(e.searchtext)) ar.push(f);
   });
   e['sA']=getSheetNames().sA;
 return ar;
}

dup.HTML

            

  
  

     
      
  
  
     Report Date

Here's your html a little better organized. I haven't checked all of the functions but atleast the select tag is inside your form and there's just a simple html structure.



  
    
      
        
      
   
    
    
     Report Date

Cooper · Accepted Answer

This is how I've done it. This script starts off by getting a list of all spreadsheets in your account and it populates a drop down on the webapp. You then select the spreadsheet and it goes back to the server to get all of the sheets for the selected spreadsheet. Once you make your selection it then loads that entire sheet on the web app and provides the capability for you to edit the data on that sheet.

Code.gs:

function htmlSpreadsheet(ssO) {
  var br='
';
  var s='';
  var hdrRows=1;
  var ss=SpreadsheetApp.openById(ssO.id);
  var sht=ss.getSheetByName(ssO.name);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  s+='';
  for(var i=0;i' + '' + '';
      } 
      else
      {
        s+='';
  }
  s+='' + '' + '';
      }
    }
    s+='';
  s+='';
  var namehl=Utilities.formatString('%s', ss.getName());
  var shnamehl=Utilities.formatString('%s', sht.getName());
  var opO={hl:s,name:namehl,shname:shnamehl};
  return opO;
}

function updateSpreadsheet(updObj) {
  var i=updObj.rowIndex;
  var j=updObj.colIndex;
  var value=updObj.value;
  var ss=SpreadsheetApp.openById(updObj.id);
  var sht=ss.getSheetByName(updObj.name);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  rngA[i][j]=value;
  rng.setValues(rngA);
  var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
  return data;
}

function doGet() {
  var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
  return userInterface.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function getAllSpreadSheets() {
  var files=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS); 
  var s = '';
  var vA=[['Select Spreadsheet',0]];
  while(files.hasNext())
  {
    var file = files.next();
    var fileName=file.getName();
    var fileId=file.getId();
    vA.push([fileName,fileId]);
  }
  //return vA;
  return {array:vA,type:'sel1'};
}

//working on this function right now 2017/11/08
function getAllSheets(ssO) {
  var ss=SpreadsheetApp.openById(ssO.id);
  var allSheets=ss.getSheets();
  var vA=[['Select Sheet']];
  for(var i=0;i



htmlss.html:



  
    
    
    
  
  
  


  

  

  
  
  
  



I did this several years ago but I still use it occasionally.

How to get info from google sheets based on drop list selection?

Answers (1)

Related Questions