Pushing Data From Spreadsheets Using App Scripts

Question

So I'm trying to write a script on Google App Scripts that does the following: On a spreadsheet with several columns and rows, whenever someone checks a checkbox on a cetain row, push the data existing on that same row, but on a different column, into a Slack message.

**For instance, let's say A1 in my spreadsheet is 'XX02' and Q1 is a checkbox. If someone comes and checks Q1, I want to send an automated message saying "XX02 done". **

Here's what I got so far:

var url = "slack url"

function onEdit(e){

var column = e.getColumn;
var row = e.getRow
var newValue = e.value; 
var oldValue = e.oldValue; 

var payload = {
      'text' : CELLVALUE + " changed from " + oldValue + " to " + newValue  + ":white_check_mark:"
    };
var params = {
      'method' : 'post',
      'contentType' : 'application/json',
      'payload' : JSON.stringify(payload)
    };
  
  return UrlFetchApp.fetch(url, params)

}

So of course, "CELLVALUE" is a placeholder there cause I have no idea how to get that value. Because I need it to work regardless of whatever box I check.

So far, what I get on Slack using this code is a message saying this "undefined changed from undefined to TRUE".

Thanks in advance to anyone who can give me a hint.

Answer 1

Assuming the target column is left next to that of the checkbox:

var range = e.range;
var cell = range.offset(0, -1);
var CELLVALUE = cell.getValue();

Reference: offset(rowOffset, columnOffset)