时间戳

首页 > 解决方案 > 如何根据下拉列表选择从谷歌表格中获取信息？

javascript - 如何根据下拉列表选择从谷歌表格中获取信息？

问题描述

在我的 webapp 中显示一个下拉列表，该列表从 google sheet 文件中获取工作表名称，因此我试图根据从 drop list 中选择的工作表获取工作表信息。该代码有效，但不基于下拉列表的选择。我做错了什么？这是完整的示例，希望它有助于识别问题

代码.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;
}

复制.HTML

            <!DOCTYPE html>
<html>
  <head>
  <base target="_top">

     <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
  </head>
  <body>
     <select id="sel1"></select><label for="sel1">Report Date </label>
    </body>   
      <script>

       $(function(){
      google.script.run
      .withSuccessHandler(function(sObj){
        var select=document.getElementById('sel1');      
        sObj.sA.unshift('Please Select a report date');
        select.options.length=0;
        for(var i=0;i<sObj.sA.length;i++) {
          select.options[i]=new Option(sObj.sA[i],sObj.sA[i]);
        }
      })
      .getSheetNames();
    });

   const loaded = new Promise((res, rej) => {
        google.charts.load('current');
        google.charts.setOnLoadCallback(res);
      });
      let wrapper = null;

      async function drawTable(arr) {
        await loaded; //wait if charts is not loaded
        wrapper = new google.visualization.ChartWrapper({
          chartType: 'Table',
          dataTable: arr,
          containerId: 'table_div',
        });
        wrapper.draw();
      }

      function getData(f) {
        google.script.run
          .withSuccessHandler(drawTable,function(rObj){
        $('#sel1').css('background-color','#ffffff');
        var select=document.getElementById('sel1');      
        rObj.sA.unshift('Please Select by Report Date');
        select.options.length=0;
        for(var i=0;i<rObj.sA.length;i++) {
          select.options[i]=new Option(rObj.sA[i],rObj.sA[i]);
        }
      })
         .getDataFromServer(f);
      }


    </script>

    <body>
    <form>

      <input  type="button"id="display"class="btn btn-primary" value="retrieve report Data" onClick="getData(this.parentNode)" />
    </form>


  <div id="table_div"></div>
  </body>
</html>

这是您的 html 组织得更好一些。我没有检查所有功能，但至少选择标签在您的表单内，并且只有一个简单的 html 结构。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
      <script src="https://www.gstatic.com/charts/loader.js"></script>
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
      <script>
       $(function(){
      google.script.run
      .withSuccessHandler(function(sObj){
        var select=document.getElementById('sel1');      
        sObj.sA.unshift('Please Select a report date');
        select.options.length=0;
        for(var i=0;i<sObj.sA.length;i++) {
          select.options[i]=new Option(sObj.sA[i],sObj.sA[i]);
        }
      })
      .getSheetNames();
    });
       const loaded = new Promise((res, rej) => {
        google.charts.load('current');
        google.charts.setOnLoadCallback(res);
      });
      let wrapper = null;

      async function drawTable(arr) {
        await loaded; //wait if charts is not loaded
        wrapper = new google.visualization.ChartWrapper({
          chartType: 'Table',
          dataTable: arr,
          containerId: 'table_div',
        });
        wrapper.draw();
      }

      function getData(f) {
        google.script.run
          .withSuccessHandler(drawTable,function(rObj){
        $('#sel1').css('background-color','#ffffff');
        var select=document.getElementById('sel1');      
        rObj.sA.unshift('Please Select by Report Date');
        select.options.length=0;
        for(var i=0;i<rObj.sA.length;i++) {
          select.options[i]=new Option(rObj.sA[i],rObj.sA[i]);
        }
      })
         .getDataFromServer(f);
      }
    </script>
   </head>
    <body>
    <form>
     <select id="sel1"></select><label for="sel1">Report Date </label>
      <input  type="button"id="display"class="btn btn-primary" value="retrieve report Data" onClick="getData(this.parentNode)" />
    </form>
  </body>
</html>

标签： javascriptjquerygoogle-apps-scriptgoogle-sheetsweb-applications

解决方案

我就是这样做的。该脚本首先获取您帐户中所有电子表格的列表，然后填充 web 应用程序上的下拉列表。然后，您选择电子表格，它会返回服务器以获取所选电子表格的所有工作表。一旦您做出选择，它就会在 Web 应用程序上加载整个工作表，并为您提供编辑该工作表上数据的能力。

代码.gs：

function htmlSpreadsheet(ssO) {
  var br='<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+='<table>';
  for(var i=0;i<rngA.length;i++)
  {
    s+='<tr>';
    for(var j=0;j<rngA[i].length;j++)
    {
      if(i<hdrRows)
      {
        s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      } 
      else
      {
        s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      }
    }
    s+='</tr>';
  }
  s+='</table>';
  s+='</body></html>';
  var namehl=Utilities.formatString('<h1>%s</h1>', ss.getName());
  var shnamehl=Utilities.formatString('<h2>%s</h2>', 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<allSheets.length;i++)
  {
    var name=allSheets[i].getName();
    vA.push([name]);
  }
  return {array:vA,type:'sel2'};
}

htmlss.html：

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {
      $('#msg').html('Please wait. Collecting a list of all Spreadsheets...');
      google.script.run
         .withSuccessHandler(updateSelect)
         .getAllSpreadSheets();
    });

    function updateSS(i,j)
    {
      var str='#txt' + String(i) + String(j);
      var value=$(str).val();
      var ssId=$('#sel1').val();
      var name=$('#sel2').val();
      var updObj={rowIndex:i,colIndex:j,value:value,id:ssId,name:name};
      $(str).css('background-color','#ffff00');
      google.script.run
         .withSuccessHandler(updateSheet)
         .updateSpreadsheet(updObj);
    }

    function updateSheet(data)
    {
      //$('#success').text(data.message);
      $('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
    }

    function updateSelect(dtO)
    {
      $('#sel1').css('background','#ffffff');
      $('#sel2').css('background','#ffffff');
      $('#msg').html('Spreadsheet List has been updated.  Now select a SpreadSheet to display');
      var select = document.getElementById(dtO.type);
      var vA=dtO.array;
      select.options.length = 0; 
      for(var i=0;i<vA.length;i++)
      {
        select.options[i] = new Option(vA[i][0],vA[i][vA[i].length-1]);
      }
    }

    function loadSelectSheet()
    {
       var shId=$('#sel1').val();
       var name=$('#sel1').text();
       $('#sel1').css('background','#ffff00');
       document.getElementById('ssname').innerHTML="";
       var ssO={name:name ,id:shId}
       google.script.run
           .withSuccessHandler(updateSelect)
           .getAllSheets(ssO);

    }

    function displaySelectedSheet()
    {
       var ssId=$('#sel1').val();
       var name=$('#sel2').val();
       $('#sel2').css('background','#ffff00');
       document.getElementById('shname').innerHTML="";
       var ssO={id:ssId,name:name};
       google.script.run
           .withSuccessHandler(displaySheet)
           .htmlSpreadsheet(ssO);
    }

    function displaySheet(opO)
    {
        $('#sel2').css('background','#ffffff');
        document.getElementById('ssname').innerHTML=opO.name;
        document.getElementById('shname').innerHTML=opO.shname;
        document.getElementById('sss').innerHTML=opO.hl;
    }

    console.log('My Code');
    </script>
    <style>
      th{text-align:left}
    </style>
  </head>
  <body>
  <div id="msg"></div><br />
  <br /><select id="sel1" onChange="loadSelectSheet();"></select>
  <br /><select id="sel2" onChange="displaySelectedSheet();"></select>
  <div id="ssname"></div>
  <div id="shname"></div>
  <div id="sss"></div>
  </body>
</html>

几年前我这样做了，但我仍然偶尔使用它。

推荐阅读