需要查询Google表格以获取来自Google Apps脚本的特定数据 [英] Need to Query Google Sheet for specific data from Google Apps Script

查看：42
本文介绍了需要查询Google表格以获取来自Google Apps脚本的特定数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我创建了一个网络应用程序来跟踪公司员工的工作时间，该应用程序很简单，它首先要求他们提供用户名和密码，然后允许他们注册其进入公司的时间退出时间.我的老板还问我，是否有一种方法可以让员工查看最近的3次注册，以防他们可能忘记记录进出时间.因此，我需要做的是在接收数据的工作表中查找每个员工的特定姓名/编号，并在最后一次注册"的最后一次注册"信息下在Web应用程序上打印该数据.部分.

I have created a web app to track the working hours of employees at my company, the web app is simple, it first asks them to provide their username and a password, and then allows them to register their time of entry o their exit time. My boss has also asked me if there is a way that employees can see what their last 3 registrations were, in case they may have forgotten to record an entry or exit time. So what I need to do is look for each employee's specific name/id in the sheet where the data is being received, and print that data on the web app under a "Last Registrations" section.

这是一个最小的可复制示例，该示例仅询问一个名称，在数据库中查找该名称，然后允许您在提交提交时间戳的同时发送提交.我所需要看到的是如何在"ReceivedData"数据库中查找该人的名字.(来自网络应用程序上的较早注册)，以及如何将来自该提交的时间戳记带到Web应用程序的最后注册部分"下.这也是一个包含一些数据的电子表格，您可以在其中工作.( https://docs.google.com/spreadsheets/d1CJoPuq3C/edit#gid = 0 )

Here is a minimal reproducible example, that just asks for a name, looks for that name in a data base and then allows you to send your submission while bringing the timestamp of the submission. All i would need to see is how to look for the person's name in the data base of "ReceivedData" (which comes from earlier registrations on the the web app) and how to bring the timestamp from that submission to the web app under the "Last Registrations section". Here is also a spreadsheet with some data where you can work. (https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0)

很抱歉，详细说明，谢谢.

Sorry for the long description and thank you.

var name="";

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Form');
}
function AddRecord(Name) {
  
  // get spreadsheet details
  var url = 'https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0';
  //Paste URL of GOOGLE SHEET
  var ss1= SpreadsheetApp.openByUrl(url);
  var webAppSheet1 = ss1.getActiveSheet();
  const Lrow = webAppSheet1.getLastRow();
  const data = [Name, new Date ()];

  webAppSheet1.getRange(Lrow+1,1, 1, data.length).setValues([data])       
}

function checkLogin(Name) {
  var url = 'https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0';
  //Paste URL of GOOGLE SHEET
  var ss2= SpreadsheetApp.openByUrl(url);
  var webAppSheet2 = ss2.getSheetByName("DataBase");
  var getLastRow =  webAppSheet2.getLastRow();
  var found_record = '';
  for(var i = 1; i <= getLastRow; i++)
  {
   if(webAppSheet2.getRange(i, 1).getValue() == Name)
   {
     found_record = 'TRUE';
   }    
  }
  if(found_record == '')
  {
    found_record = 'FALSE'; 
  }
  
  return found_record;
  
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
    function AddRow()
    {
      var Name = document.getElementById("Name").value;  
      google.script.run.AddRecord(Name);
      document.getElementById("Name").value = '';
      }    
          function LoginUser()
    {
    var Name = document.getElementById("Name").value;
    
    google.script.run.withSuccessHandler(function(output) 
    {
      if(output == 'TRUE')
      {
        document.getElementById("loginDisplay").style.display = "none";
        document.getElementById("dataDisplay").style.display = "block";     
      }
      else if(output == 'FALSE')
      {
        document.getElementById("errorMessage").innerHTML = "Name not found";     
      }    
    }).checkLogin(Name);
    }
    </script>
</head>
<body>
<div id="loginDisplay">
    <div> 
    <label>Name</label><br>
    <input type="text" id="Name" />
    </div>
    
    <div class="btn">
<input value="Login" onclick="LoginUser()"
 type="button">
 <span id="errorMessage"></span>
</div> 

    </div>
    <div style="display:none"  id="dataDisplay">
    <div>
    <label>Last Registrations</label>
    <br><br>
    <button type="button" value="Add" onclick="AddRow()">Send</button>
</div>
</div>
  </body>
</html>

推荐答案

我相信您的目标如下.

  • 来自所以我需要做的是在接收数据的工作表中查找每个员工的特定姓名/编号，并在最后一次注册"下将该数据打印在Web应用程序上.和我所需要看到的就是如何在"ReceivedData"数据库中查找此人的姓名.(来自Web应用程序上的早期注册)以及如何将提交的时间戳记带到最后注册部分"下的Web应用程序.，我理解您的问题如下.

  • From So what I need to do is look for each employee's specific name/id in the sheet where the data is being received, and print that data on the web app under a "Last Registrations" section. and All i would need to see is how to look for the person's name in the data base of "ReceivedData" (which comes from earlier registrations on the the web app) and how to bring the timestamp from that submission to the web app under the "Last Registrations section"., I understood your question as follows.

  1. 您要从电子表格的 ReceivedData 表中搜索输入的值.
  2. 您希望将通过从表单中搜索输入的值检索到的最新时间戳记放置在最后注册部分"的下方.

  • 从您的脚本来看，我认为当GAS端的功能 checkLogin 中包含用于搜索输入值的脚本时，
  • 并且，为了显示最后一个时间戳，将< div id ="lastTimestamp"></div> 添加到HTML，并在其中添加 LoginUser Javascript已修改.
  • From your script, I thought that when the script for searching the inputted value is included in the function checkLogin in GAS side, it might be suitable.
  • And, in order to show the last timestamp, <div id="lastTimestamp"></div> is added to HTML and LoginUser in Javascript is modified.

当以上几点反映到您的脚本中时，它如下所示.

When above points are reflected to your script, it becomes as follows.

请按如下所示修改 checkLogin .

function checkLogin(Name) {
  var url = 'https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0'; //Paste URL of GOOGLE SHEET
  var ss2= SpreadsheetApp.openByUrl(url);
  var webAppSheet2 = ss2.getSheetByName("DataBase");
  var check = webAppSheet2.getRange(2, 1, webAppSheet2.getLastRow(), 1).createTextFinder(Name).matchEntireCell(true).findNext();
  var obj = {check: check ? 'TRUE' : 'FALSE'};
  var sheet = ss2.getSheetByName("ReceivedData");
  var ranges = sheet.getRange(2, 1, sheet.getLastRow(), 1).createTextFinder(Name).matchEntireCell(true).findAll();
  if (ranges.length > 0) {
    obj.lastTimestamp = ranges.pop().offset(0, 1, 1, 1).getDisplayValue();
    return obj;
  }
  return obj;
}

HTML&Javascript方面:

请按如下所示修改 LoginUser .

function LoginUser() {
  var Name = document.getElementById("Name").value;
  google.script.run.withSuccessHandler(function({check, lastTimestamp}) {
    if(check == 'TRUE') {
      document.getElementById("loginDisplay").style.display = "none";
      document.getElementById("dataDisplay").style.display = "block";
      document.getElementById("lastTimestamp").innerHTML = lastTimestamp;
    } else if(check == 'FALSE') {
      document.getElementById("errorMessage").innerHTML = "Name not found";
    }
  }).checkLogin(Name);
}

并且，请按如下所示添加HTML.

And, please add HTML as follows.

<label>Last Registrations</label>
<br><br>
<button type="button" value="Add" onclick="AddRow()">Send</button>

至:

<label>Last Registrations</label>
<br><br>
<div id="lastTimestamp"></div>
<button type="button" value="Add" onclick="AddRow()">Send</button>

注意:

  • 在此修改后的脚本中，使用了共享的电子表格和脚本.因此，当电子表格的结构发生更改时，该脚本可能无法使用.请注意这一点.

    • 这篇关于需要查询Google表格以获取来自Google Apps脚本的特定数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
前端开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆