javascript - 如何连接一些函数以获得结果

Question

UPD:决定

这是 script.google.com 问题。

我有包含信息的电子表格(行-数据\列-时间)，里面的每个单元格都是可用“空间”的数量。

Example:

Date\Time | 11:00:00 | 15:00:00 | 17:00:00
01.09.2019|    10    |    15    |    30
02.09.2019|    10    |    15    |    30
03.09.2019|    10    |    15    |    30
04.09.2019|    10    |    15    |    30

在 Web 表单中输入日期和时间后 - 程序应该显示有多少“空间”是可用的。

• Web 表单后现在有 3 个步骤:
  1. 从表单(数据)获取行
  2. 从表单中获取列(时间)
  3. 获取单元格值并以表单返回(sheet.getRange(row, col).getValue();)

如何将3个函数连接在一起？

// Get information from form

<script>   
function getEmptySpace(){       
     var presentDate = document.getElementById("dt").value;
     var presentTime = document.getElementById("tm").value;    
     if(presentDate.length >= 10 && presentTime.length >= 8){
  google.script.run.withSuccessHandler(updateEmptySpaceAdult).getSpaceAdult(presentDate,presentTime);             
       }  
     }

     function updateEmptySpaceAdult(spaceAdult){      
        document.getElementById("anav").value = spaceAdult;        
     }
</script>

// My 3 functions, which sholud get cell value and return it in form

//Function 1 - getRowNum

function getRowNum(){
  var sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Ticket");
  var data = sheet.getDataRange().getValues();
  var name = "19.12.2018";  //Here should be arg "presentDate" (.getSpaceAdult(presentDate,presentTime);)
  for(var i = 0; i<data.length;i++){
    if(data[i][0] == name){ //[0] because serch in column A      
      return i+1;
    }
  }
}

//Function 2 - getColNum

function getColNum() {
  var sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Ticket");
  var time = "17:00:00"; //Here should be arg "presentTime" (.getSpaceAdult(presentDate,presentTime);)
  var range = sheet.getRange(2, 1, 1, sheet.getMaxColumns());
  var values = range.getValues();
  
  for (var row in values) {
    for (var col in values[row]) {
      if (values[row][col] == time) {
         return parseInt(col) + 1;
      }
    }
  }
}

//Function 3 - getCellValue

function getCellValue(){
var sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Ticket");
var row = 8.0;
var col = 4.0;

var value = sheet.getRange(row, col).getValue();
}

// I think main Function is - .getSpaceAdult(presentDate,presentTime)

function getSpaceAdult(presentDate,presentTime){
*Call Function 1 - getRowNum() with arg "presentDate" and get RowNumber
*Call Function 2 - getColNum() with arg "presentTime" and get ColNumber
*Call Function 3 - getCellValue() by returns "getRowNum and getColNum" and show tabel cell value
In the end function getSpaceAdult = tabel cell value
} 

Answer 1

阅读和练习:

• Functions MDN reference
• Legal parameters
• Compare dates

脚本逻辑:

• 声明所有带有适当参数的函数
• 删除每个函数中对电子表格服务的重复调用。

片段:

function getRowNum(date, sheet){
  //receives two arguments date and sheet.
  //DO NOT REDCLARE date and sheet using `var`: var sheet =... or var date =
  //REMOVEDvar sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Ticket");
  var data = sheet.getDataRange().getValues();
  //REMOVEDvar name = "19.12.2018";  //Here should be arg "presentDate" (.getSpaceAdult(presentDate,presentTime);)
  for(var i = 0; i<data.length;i++){
    if(data[i][0] == date){ //[0] because serch in column A      
      return i+1;
    }
  }
}

function getColNum(time, sheet) {
  //receives two arguments time and sheet.
  //DO NOT REDCLARE date and sheet using `var`: var sheet =... or var time =
  /*Rest of your code except  declarations of time/ sheet
   *
   */
}

function getSpaceAdult(date, time){
  var sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Ticket");
  var row = getRowNum(date, sheet); //Get row number passing on two arguments date and sheet
  var col = getColNum(time, sheet);
  var value = sheet.getRange(row, col).getValue();
  return value;//Added
}

注释:

• getValues() 返回 Object[][]:部分/全部接收到的值可能是日期对象。日期对象不能与 == 进行比较。您可以使用 getDisplayValues() 来获取字符串形式的日期。