How to use offset after obtaining reference cell using indexOf in Google Script

0
votes

I've written a script to find a match of a given cell via for loop variable (it works great). When logged out, it provides the position 11.0 in the searched column of data. What I then want to do is uncheck a checkbox that is offset by (0,-5) from that referenced cell. When I try to use offset, I get the TypeError: offset is not a function. How do I accomplish this?

Below is the script I've written to get to where I am stuck. What I am doing is checking a box on Sheet2, which is on the same row for a given product. The row on which the product and its checkbox reside will not always be the same (could be row 5, could be row 12). Once I check the box, the script determines what the name of the product is (in this case, Product L), and then looks for its match via indexOf on Sheet1. IndexOf finds the location on Sheet1 (in this case, 11.0, which is row 13, col 10). There is a corresponding checkbox on Sheet1 row 13 in column 5 which then needs to be unchecked. And that is where I am stuck. The sheet is dynamic, and the rows for checkboxes and corresponding products will constantly be changing.

function findText() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet2 = spreadsheet.getSheetByName("Sheet2");
  var sheet1 = spreadsheet.getSheetByName("Sheet1");
  var lr = sheet1.getLastRow();
  var lookupRangeArray = sheet1.getRange(3,10,lr,1).getValues();
  var newRange = lookupRangeArray.map(function(r){ return r[0]; });    
 for (var i=0; i <= lr; i++){
   var checkboxRange = sheet2.getRange(i+1,2);
   var checkboxStatus = checkboxRange.getValue();
  if(checkboxStatus == true){  
   var text = checkboxRange.offset(0, 2).getValue();
   Logger.log(text);
   var position = newRange.indexOf(text);
   Logger.log(position)
   position.offset(0,-5).setValue(false);
1
google-apps-scriptgoogle-sheetsoffsetindexof

1 Answers

0
votes

You need to retrieve the cell to offset

You can do it e.g. by specifying the row and column position of the cell reference or its A1 notation.

Sample

var row=1;
var column = 11;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange(row, column);
var newCell = cell.offset(0, -5);