单元格编辑时触发API调用

问题描述

使用Google Sheets，其中每行都包含ID和选择列表值。示例：Google Sheet。

我尝试做的是在有人编辑选择列表单元格时运行自定义函数。该函数接受两个参数，来自同一行的ID和PickList单元格的值，然后执行一个HTTP POST请求以更新我们的CRM中的记录。

//When STAGE cell on Google Sheet is updated, run this function:

function updateProjectStage(status, id) {
  var baseURL = 'https://crm.zoho.com/crm/private/json/Potentials/updateRecords?authtoken=xxx&scope=crmapi&id=', // see docs https://www.zoho.com/crm/help/api/updaterecords.html
      recordID = id, // building id from A column
      stage = '<Potentials><row no="1"><FL val="Stage">' + status + '</FL></row></Potentials>'; // status from B column

  var postURL = baseURL + recordID + '&xmlData=' + stage;
  Logger.log(postURL);

  var response = UrlFetchApp.fetch(postURL); // update record in crm
  var sanitizedResponse = JSON.parse(response.getContentText()); // get confirmation/failure
  Logger.log(sanitizedResponse);
}

我不知道如何为这种选择列表类型的单元格运行函数-我不能像我习惯的那样只向单元格中输入=updateProjectStage(status, id)，因为它会出错。

示例：Error Message.

这可能吗？

推荐答案

您的答案在于在用户修改工作表上的任何单元格时捕获编辑事件。当然，用户可以修改任何单元格。您的工作是确定该单元格是否在您关心的范围内。可以使用以下函数捕获onEdit事件：

function onEdit(eventObj) {
  //--- check if the edited cell is in range, then call your function
  //    with the appropriate parameters
}

传入事件的对象描述被编辑的单元格。因此，我们设置了一个"检查范围"，然后将该范围与任何编辑过的单元格进行比较。下面是函数：

function isInRange(checkRange, targetCell) {
  //--- check the target cell's row and column against the given
  //    checkrange area and return True if the target cell is
  //    inside that range
  var targetRow = targetCell.getRow();
  if (targetRow < checkRange.getRow() || targetRow > checkRange.getLastRow()) return false;

  var targetColumn = targetCell.getColumn();
  if (targetColumn < checkRange.getColumn() || targetColumn > checkRange.getLastColumn()) return false;

  //--- the target cell is in the range!
  return true;
}

编辑事件的完整事件函数为

function onEdit(eventObj) {
  //--- you could set up a dynamic named range for this area to make it easier
  var checkRange = SpreadsheetApp.getActiveSheet().getRange("B2:B10");  
  if (isInRange(checkRange, eventObj.range)) {
    //--- the ID cell is on the same row, one cell to the left
    var idCell = eventObj.range.offset(0,-1);
    //--- the status cell is the one that was edited
    var statusCell = eventObj.range;
    updateProjectStage(statusCell, idCell);
  }
}

以下是全部内容：

function isInRange(checkRange, targetCell) {
  Logger.log('checking isInRange');

  //--- check the target cell's row and column against the given
  //    checkrange area and return True if the target cell is
  //    inside that range
  var targetRow = targetCell.getRow();
  if (targetRow < checkRange.getRow() || targetRow > checkRange.getLastRow()) return false;
  Logger.log('not outside the rows');

  var targetColumn = targetCell.getColumn();
  if (targetColumn < checkRange.getColumn() || targetColumn > checkRange.getLastColumn()) return false;
  Logger.log('not outside the columns');

  //--- the target cell is in the range!
  return true;
}

function onEdit(eventObj) {
  //--- you could set up a dynamic named range for this area to make it easier
  var checkRange = SpreadsheetApp.getActiveSheet().getRange("B2:B10");  
  if (isInRange(checkRange, eventObj.range)) {
    Logger.log('cell is in range');
    //--- the ID cell is on the same row, one cell to the left
    var idCell = eventObj.range.offset(0,-1);
    //--- the status cell is the one that was edited
    var statusCell = eventObj.range;
    updateProjectStage(statusCell, idCell);
  }  else {
    Logger.log('must be outside the range');
  }
}

function updateProjectStage(status, id) {
  Logger.log('we are updating');
}

这篇关于单元格编辑时触发API调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！