Trigger API call when cell is edited

Question

Working with a Google Sheets where every line includes an ID and a picklist value. Example: Google Sheet.

What I am trying to do is run a custom function when someone edits the picklist cell. The function takes two arguments, the value from the ID and the picklist cell of the same line, then performs a HTTP POST request to update the record in our 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);
}

I don't know how to run the function for this picklist type of cell - I cannot just input =updateProjectStage(status, id) into the cell like I am used to doing because it errors out.

Example: Error Message.

Is this even possible?

It seems like you're not having any trouble with your Go code, just how to make an API call from Google Sheets, so this may be better suited to Super User. — Adrian
Well this has to be written in golang, so I don't know why you'd say that - it's exactly what I'm having trouble with. I don't even know what to write; surely there must be some helper functions to handle events - but can they be used in a Google Sheets custom function script? — Deven Blackburn
Google Sheets scripts run in the browser, Go does not. I thought you were running this Go code in some kind of service, maybe I'm not understanding your question well. — Adrian

PeterT PeterT · Accepted Answer · 2018-08-10T19:24:16

Your answer lies in capturing the edit event when the user modifies any cell on the sheet. The user can modify any cell, of course. Your job is to determine if that cell is in the range you care about. The onEdit event can be captured using this function:

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

The object passed into the event describes the cell that was edited. So we set up a "check range" and then make a comparison of that range to whichever cell was edited. Here's the function:

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;
}

The full event function for the edit event would be

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);
  }
}

Here's the whole thing all together:

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');
}

Trigger API call when cell is edited

1 Answers