时间戳

首页 > 解决方案 > 需要一个自定义函数来为显示的每一行重复并最小化 API 调用

arrays - 需要一个自定义函数来为显示的每一行重复并最小化 API 调用

问题描述

编辑：我需要帮助将功能组合成 1 并添加触发器。

在我的电子表格中，我4-100通过选择“服务月”或“服务日”（即“7/11”仅显示 5 行“7 月”将显示）从“ServiceData”工作表中筛选出客户服务呼叫行65 行）。每个行项目在列中具有对应的起点/目的地的地点 ID，在列K中L具有订单号（如当天的第 1、2、3... 次服务调用）J。

旅行时间电子表格

我travelTime()在单元格中使用以下自定义函数M4:M100来计算 2 个地点 ID 之间的行驶时间和距离：

function travelTime(origin,destination) {
  var API_KEY = PropertiesService.getScriptProperties().getProperty('keyMaps');
  var baseUrl = "https://maps.googleapis.com/maps/api/distancematrix/json? units=imperial&origins=";
  var queryUrl = baseUrl + "place_id:" + origin + "&destinations=" + 
"place_id:" + destination + "&mode=driving" + "&key=" + API_KEY;
  var response = UrlFetchApp.fetch(queryUrl);
  var json = response.getContentText();
  var time = JSON.parse(json);

  return [[ time.rows[0].elements[0].duration.text,
            time.rows[0].elements[0].distance.text ]] ;

一个主要问题是，当我对“ServiceData”电子表格进行编辑时（即，当特定日期被过度安排时，服务日期会更改）并且不需要更新旅行时间，直到我按计划完成了工作。经过大量研究后，我似乎可以使用几个选项；缓存、循环、数组，并将所有内容放入脚本中，然后附加到按钮以仅在准备好时运行。考虑到我是一个新手，将所有这些选项放在一起肯定超出了我的技能水平，并且真的可以使用一些帮助。

使用新功能进行编辑：
因此，经过更多研究，我能够将以下功能放在一起，当每个功能独立运行时效果很好。现在我遇到的问题是将这些全部放在一起，特别是将原始文件调整travelTime()为newTravelTime(). 我已经朝着下面的正确方向进行了尝试，但不知道如何在那里进行 API 调用。 

function newTravelTime() {//<--**having issues how to write this function
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MonthlySA");
  var sourceR = sourceSheet.getRange(4, 11, sourceSheet.getLastRow()-3, 4);
  var sourceV = sourceR.getValues();

  var array = [];
  for (var i = 0; i < sourceV.length; i++) {
    if (sourceV[i][2] == "") {
      var origin = sourceV[i][0];//ori place IDs for API query
      var destination = sourceV[i][1];//des place IDs API api query
    }
    array.push([sourceV[i][2]]);
  }
  sourceSheet.getRange(4, 13, array.length, 1).setValues(array);

我想创建一个getTravelTime()包含所有功能的决赛，并在单元格中的“服务月”或“服务日”更改B1或B2运行它们时添加一个 OnEdit 触发器。如果对我的功能本身有任何建议，我将非常感谢一些帮助，我对此非常陌生并且正在尝试。

///checks if origin/destination are already in the cacheSheet then return travel time to sourceSheet
function getCachedTravelTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MonthlySA");
  var sourceR = sourceSheet.getRange(4, 11, sourceSheet.getLastRow()-3, 4);
  var sourceV = sourceR.getValues();
  var cacheSheet = ss.getSheetByName("TravelTimeCache");
  var cacheR = cacheSheet.getRange(2, 1, cacheSheet.getLastRow()-1, 4); 
  var cacheV = cacheR.getValues();

  var array = [];
  for (var i = 0; i < sourceV.length; i++) {
    for (var j = 0; j < cacheV.length; j++) {
      //if origin/destination columns from sourceSheet match columns on cacheSheet
      if (sourceV[i][0]+sourceV[i][1] == cacheV[j][0]+cacheV[j][1]) {
        sourceV[i][2] = cacheV[j][2]; //column with travel duration
        sourceV[i][3] = cacheV[j][3]; //column with travel distance
      }
    }
    array.push([sourceV[i][2], sourceV[i][3]]);
  }
  sourceSheet.getRange(4, 13, array.length, 2).setValues(array);
}

///if origin or destination are blank, label as 'missing value'
function missingOD() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MonthlySA");
  var sourceID = sourceSheet.getRange(4, 3, sourceSheet.getLastRow()-3, 12);
  var sourceV = sourceID.getValues();

  var array = [];
  for (var i = 0; i < sourceV.length; i++) {
    // if ID has a value
    if (sourceV[i][0] != "") {
      // if origin or destination is blank
      if (sourceV[i][8] == "" || sourceV[i][9] == "") {
        sourceV[i][10] = 'missing value';
      }
    }
    array.push([sourceV[i][10]]);
  }
  sourceSheet.getRange(4, 13, array.length, 1).setValues(array);
}

///if cache not found - get the new travelTime for that origin/destination on sourceSheet...
function newTravelTime() {//<--

}

///...and store the new travelTime() in cacheSheet
function storeTravelTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("MonthlySA");
  var sourceR = sourceSheet.getRange(4, 11, sourceSheet.getLastRow()-3, 4);
  var sourceV = sourceR.getValues();
  var cacheSheet = ss.getSheetByName("TravelTimeCache");
  var cacheR = cacheSheet.getRange(2, 1, cacheSheet.getLastRow()-1, 4); 
  var cacheV = cacheR.getValues();

  var array = [];
  for (var i = 0; i < sourceV.length; i++) {
    var duplicate = false;
    for (var j = 0; j < cacheV.length; j++) {
      if (sourceV[i][0]+sourceV[i][1] == cacheV[j][0]+cacheV[j][1]) {
        duplicate = true; 
      }
    }
    if(!duplicate){ //if origin/destination columns from sourceSheet are NOT matched on cacheSheet
    array.push([sourceV[i][0], sourceV[i][1], sourceV[i][2], sourceV[i][3]]);//columns with new data
    }
  }
  //add new data to last row of cacheSheet
  cacheSheet.getRange(cacheSheet.getLastRow()+1, 1, array.length, 4).setValues(array);
}

标签： arraysgoogle-apps-scriptgoogle-sheetsgoogle-distancematrix-apicustom-function

解决方案

想到的最简单的解决方案之一是Caching. 不是每次都进行 API 调用，而是检查我们之前是否已经进行过调用。

像这样的东西

function getTravelTime(origin, destination) {
  var travelTime = getTravelTimeFromPreviousCall(origin, destination);
  if (travelTime != null) {
    return travelTime;
  } else {
    var travelTime = fetchTravelTime(origin, destination);
    storeTravelTime(origin, destination, travelTime);
    return travelTime;
  }
}

function fetchTravelTime(origin, destination) {
  var API_KEY = PropertiesService.getScriptProperties().getProperty('keyMaps');
  var baseUrl = "https://maps.googleapis.com/maps/api/distancematrix/json? units=imperial&origins=";
  var queryUrl = baseUrl + "place_id:" + origin + "&destinations=" + "place_id:" + destination + "&mode=driving" + "&key=" + API_KEY;

  var response = UrlFetchApp.fetch(queryUrl);
  var json = response.getContentText();
  var time = JSON.parse(json);

  return time.rows[0].elements[0].duration.text;
}

为此，我们可以定义我们的缓存，例如：

带列的工作表 -

  1. 起源
  2. 目的地
  3. 旅行时间

我们需要定义以下函数：

getTravelTimeFromPreviousCall(origin, destination)：在此我们需要检查缓存并返回该出发地和目的地的旅行时间，如果没有找到则返回null

storeTravelTime(origin, destination, time)：这将仅存储行程时间以供将来在缓存表中使用

你可以尝试这样的事情：

function getTravelTimeFromPreviousCall(origin, destination) {
    var cacheSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(CACHE_SHEET_NAME);
    var cacheData = sheet.getDataRange().getValues();

    for (var i=0; i<cacheData.length; i++) {
        if (cacheData[i][ORIGIN_COLUMN_INDEX]==origin && cacheData[i][DESTINATION_COLUMN_INDEX]==destination) {
            return cacheData[i][TRAVEL_TIME_COLUMN_INDEX];
        }
    }

    return null;
}

function storeTravelTime(origin, destination, travelTime) {  
    var cacheSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(CACHE_SHEET_NAME);
    sheet.appendRow([origin, destination, travelTime]);
}

请根据您的工作表修复循环变量、数组索引和常量。

推荐阅读