2

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.

2 Answers 2

0

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:

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?

-1

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...

5
  • That answer isn't likely going to work. I'll look at it some more today and see if I can find you a solution to this. My experience with these functions lies in VBA and VB5 and VB6, VB.NET. Google Apps Script is going to have some minor differences but I've been doing some coding there lately also. I'll see what I can come up with. Apr 12, 2020 at 12:51
  • The sheet that I'm looking up is actually the same one with the button on it, so adding the sheet name didn't make it work any differently. Apr 12, 2020 at 13:56
  • No, I knew it wouldn't. I realized that later. I have been looking at it, and I've actually been doing some coding. The 'buttons' aren't really buttons so much as they are 'drawings', and I'm new to Google Apps Script to some extent, but I noticed that you can link a script to a drawing. Personally, I wouldn't even mess with the vlookup, and I had some code I had already written to handle a good bit of what you need done. I was going to post it in here when I have it working. Apr 13, 2020 at 9:58
  • I mean I already have it locating the team names from the Visitor/Home table in the corresponding list for the wins and losses. Right now I'm looking at how to best go about handling the drawing/buttons with minimal amount of coding and I think i'm going to just add a control array function so I only have to deal with the functionality of the button one single time for all the buttons on the left and the right. Apr 13, 2020 at 9:58
  • Like I said, once I get something functioning I'll post it here. vlookup has always been a messy function to me, it is actually confusing and I have other ways that are already done and do the same thing, so..... Apr 13, 2020 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.