Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math

I've been working on updating an existing code from an Excel sheet used in PTU (pokemon tabletop United), but i am having some struggles.

I got it to do the math in the column but it wouldn't do on an individual cell. I've tried a few different things, trying to change the data range from where it finds the box to do the calculated mathematics, but all I've gotten is to do all the cells in that column repeating what's in the top most box. Not taking the individual numbers in that columns Cells.

function addQuestXP(){
  var maxxp=0;
  var multiplier=1;
  var main_sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trainer");
  if(main_sheet===null){
    Logger.log("No Trainer Sheet found");
    maxxp=20555;
    multiplier=1
  }else{
    var level=main_sheet.getRange(2,7).getValue();
    Logger.log("Trainer level %s",level);
    if(level<6){
      maxxp=109
    }else if(level<11){
      maxxp=459;
      multiplier=1
    }else if(level<16){
      maxxp=1259;
      multiplier=1
    }else if(level<21){
      maxxp=2354;
      multiplier=2
    }else if(level<26){
      maxxp=3849;
      multiplier=2
    }else if(level<31){
      maxxp=6109;
      multiplier=3
    }else if(level<36){
      maxxp=8769;
      multiplier=3
    }else if(level<41){
      maxxp=11909;
      multiplier=4
    }else if(level<46){
      maxxp=16234;
      multiplier=4
    }else{
      maxxp=20555;
      multiplier=5
    }
  }
  var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var ui=SpreadsheetApp.getUi();
  var response=ui.prompt("How much trainer xp or base xp? DO NOT ENTER THE AMOUNT OF XP FOR YOUR TIER! THE SCRIPT WILL MULTIPLY FOR TIER AUTOMATICALLY.");
  var xp=parseInt(response.getResponseText());
  for(var i=0;i<3&&isNaN(xp);i++){
    response=ui.prompt("Please enter an actual number");
    xp=parseInt(response.getResponseText())
  }
  if(isNaN(xp)){
    ui.alert("No valid value entered, ending.");
    return
  }
  if(xp<15){
    xp*=5
  }
  xp=Math.floor(xp*multiplier);
  Logger.log("Adding %s xp to cap of %s",xp,maxxp);
  for(var i=0;i<sheets.length;i++){
    var a1=sheets[i].getRange(1,1);
    var curXPCell=sheets[i].getRange(2,11);
    if(a1.getValue()=='Pokereader'&&!isNaN(parseInt(curXPCell.getValue()))){
      Logger.log('%s has %s xp. Adding %s xp for a total of %s',sheets[i].getName(),curXPCell.getValue(),xp,Math.min(curXPCell.getValue()+xp,maxxp));
      curXPCell.setValue(Math.min(curXPCell.getValue()+xp,maxxp))
    }
  }
}

I did write this to show the common error I get, I've done it a few different ways but this is the best example of the code failure.

i=0;i<sheets.length;i++){var a1=sheets[i].getRange(1,1);var curXPCell=sheets[i].getRange(2,11,100,1);if(a1.getValue()=='Pokereader'&&!isNaN(parseInt(curXPCell.getValue()))){Logger.log('%s has %s xp. Adding %s xp for a total of %s',sheets[i].getName(),curXPCell.getValue(),xp,Math.min(curXPCell.getValue()+xp,maxxp));curXPCell.setValue(Math.min(curXPCell.getValue()+xp,maxxp))}}}

here is an example of data, the column I'm hoping to effect is the XP column to get it to multiply the TXP number by the XP multiplier for that level, its able to do it to an individual cell on multiple sheets, but not multiple cells on the same sheet I'm also not to sure what that get value is for or why its in there and I'm sorry for not providing the correct data before hopefully I got it right this time

Getting multiple values using getRange and getValues

In order to achieve your goal of getting all the values of the specific range of a sheet and do the calculations. You may need to use the 4 parameters of getRange() and use the getValues() method.

var levels = main_sheet.getRange(2, 7, main_sheet.getLastRow() - 1, 1).getValues();

Meanwhile, to do calculations considering all of the levels, you can use a for loop.

for (var row = 0; row < levels.length; row++) {

Here is the full modified script:

function addQuestXP() {
  var maxxp = 0;
  var multiplier = 1;
  var main_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trainer");

  if (main_sheet === null) {
    Logger.log("No Trainer Sheet found");
    maxxp = 20555;
    multiplier = 1;
  }
  else {
    var levels = main_sheet.getRange(2, 7, main_sheet.getLastRow() - 1, 1).getValues();
    var ui = SpreadsheetApp.getUi();
    var response = ui.prompt("How much trainer xp or base xp? DO NOT ENTER THE AMOUNT OF XP FOR YOUR TIER! THE SCRIPT WILL MULTIPLY FOR TIER AUTOMATICALLY.");
    var xp = parseInt(response.getResponseText());

    for (var i = 0; i < 3 && isNaN(xp); i++) {
      response = ui.prompt("Please enter an actual number");
      xp = parseInt(response.getResponseText());
    }

    if (isNaN(xp)) {
      ui.alert("No valid value entered, ending.");
      return;
    }

    if (xp < 15) {
      xp *= 5;
    }

    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var row = 0; row < levels.length; row++) {
      var level = levels[row][0];
      if (level < 6) {
        maxxp = 109;
        multiplier = 1;
      } else if (level < 11) {
        maxxp = 459;
        multiplier = 1;
      } else if (level < 16) {
        maxxp = 1259;
        multiplier = 1;
      } else if (level < 21) {
        maxxp = 2354;
        multiplier = 2;
      } else if (level < 26) {
        maxxp = 3849;
        multiplier = 2;
      } else if (level < 31) {
        maxxp = 6109;
        multiplier = 3;
      } else if (level < 36) {
        maxxp = 8769;
        multiplier = 3;
      } else if (level < 41) {
        maxxp = 11909;
        multiplier = 4;
      } else if (level < 46) {
        maxxp = 16234;
        multiplier = 4;
      } else {
        maxxp = 20555;
        multiplier = 5;
      }

      var updatedXP = Math.floor(xp * multiplier);
      Logger.log("Adding %s xp to cap of %s", xp, maxxp);

      for (var i = 0; i < sheets.length; i++) {
        var a1 = sheets[i].getRange(1, 1);
        var curXPCell = sheets[i].getRange(row + 2, 11);

        if (a1.getValue() == 'Pokereader' && !isNaN(parseInt(curXPCell.getValue()))) {
          Logger.log('%s has %s xp. Adding %s xp for a total of %s', sheets[i].getName(), curXPCell.getValue(), updatedXP, Math.min(curXPCell.getValue() + updatedXP, maxxp));
          curXPCell.setValue(Math.min(curXPCell.getValue() + updatedXP, maxxp));
        }
      }
    }
  }
}

Sample Dataset and output:

Execution log:

References:

• getRange(row, column, numRows, numColumns)
• getValues()
• For loop