Changing info on a different sheet in the same spreadsheet

Question

I have two ranges of equal size on different sheets in the same spreadsheet. I am trying to find a row (based off of user input) in the first sheet and then use that index to modify a table in the second sheet that counts how many times that certain index has been used before (to make a nice looking pie chart).

This code runs but will not produce results on the second sheet. I've gone through the debugging process and my best guess is that for some reason, my for in loop is not running through. Attached is my code that takes in the beforementioned index and attempts to perform the second half of my goal.

function acceptToEncounterChart(ghostrow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(ss.getSheets()[1]);
  ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Average Encounter Chart");
  var range = sheet.getRange("B3:B14")
  
  for(var i in range) {
    if(ghostrow == i) {
      var before = range[i][0].getValue()
      range[i][0].setValue(before + 1);
    } 
  }
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
};

user_11225291 · Answer

Explanation:

I am not entirely sure what is your goal.

However, here is some fixes / improvements starting from the beginning:

You define 2 times the same variable ss with exactly the same value.
You don't need to set the active sheet, if your goal is to just get the sheet, therefore this line is redundant:

SpreadsheetApp.setActiveSheet(ss.getSheets()[1]);
Variable range is not an array but a range object. You can't index it and therefore you can't also use a for loop to iterate over a single object. For the same exact reason, the code inside the if statement is wrong, you can't index range. But you don't see any errors because the if statement evaluates to false.
In JavaScript and in many other programming languages, array indexes start from 0. Since your range starts from cell B3 or row 3, you need to use i+3 to match the data with the range.
For the same reason as the previous point, ghostrow is an index, not a row. The if statement compares an array index i with ghostrow, so ghostrow should not be confused with the actual sheet row. For example, if you choose ghostrow=5 then the current script will increment the value of the cell B8 (remember i+3) by 1.

Solution:

Here is a workable code snippet:

function acceptToEncounterChart(ghostrow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Average Encounter Chart");
  var data = sheet.getRange("B3:B14").getValues().flat();
  
  data.forEach((v,i)=>{
   if(ghostrow == i){
    sheet.getRange(i+3,2).setValue(v+1)
  }              
  });
 ss.setActiveSheet(ss.getSheets()[0]);
}

Changing info on a different sheet in the same spreadsheet

1 Answers

Explanation:

Solution:

Related: