Adding 1 to a Cell with VLookup

Question

So this is my first time trying to write a script, and the sheet I am working on is here.

The script I'm trying to run would allow me to push a button and have it use Vlookup to add 1 to a cell in the matching row. For example, in my sheet, if I click the plus button to the left of the Tigers, I would want it to add 1 to their season win total in column K. I can use the script below to successfully add to a specific cell:

function win1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var yesCount = ss.getRange("k10");
var yesAdd = yesCount.getValue();
yesCount.setValue(yesAdd+1);
}

...however, when I replace "k10" with a Vlookup, I get an error that says "Exception: Range not found (line 3, file "Code")". The script I'm using ends up looking like this:

function win1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var yesCount = ss.getRange("vlookup(S2,G1:L30,5,FALSE)");
var yesAdd = yesCount.getValue();
yesCount.setValue(yesAdd+1);
}

As I said, I'm new to this, so any help would be appreciated.

Answer 1

It's not possible to use a formula as an argument of getRange, actually, it's not possible to evaluate a formula on Google Apps Script.

To learn the valid syntax for getRange, please checkout the docs:

• https://developers.google.com/apps-script/reference/spreadsheet/sheet
• https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet

To emulate the VLOOKUP function on Google Apps Script you should use JavaScript. For further details see Is there a way to emulate Vlookup in Google Script?

Answer 2

I'm pretty sure you'd have to include the sheet name if you are looking up data from a different sheet than the sheet the macro or code is being called from, for example, you might try: (and see if that works)

 function win1() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var yesCount = ss.getRange("vlookup(S2,SheetName!G1:L30,5,FALSE)");
 var yesAdd = yesCount.getValue();
 yesCount.setValue(yesAdd+1);
 }

Of course you need to replace SheetName! above with the name of the worksheet you're doing the lookup on.

It has been a minute since I've used VBA and Excel, but it looks to me like that is probably what is wrong, or something very close to that...