Skip to main content

Querying GitHub Issues from Google App Script

I'm currently exploring different integrations that can be done between Google App Script and Git. This is part of a bigger attempt to integrate Git data into my project management system. I'll post more on that later. GitHub supports a number of very nice sub-systems. One of them is the GitHub Issues ticket tracking system. It's fairly robust and comes with GitHub - which means it's highly integrated out-of-the-box. Integration with Google's App Script is done via the fetchURL command and JSON object manipulation.  After a good bit of experimentation, I've settled on querying GitHub Issues with an "on open spreadsheet event" to avoid issues with Google's quota system. Essentially, Google prevents you from calling the fetchURL command more than X times (see the quota limits ) per day. That's not great if you have people actively working and changing data in a spreadsheet. Some of my other App Script routines are running thousands of times per d

From Excel VBA to Google App Script : Part 2 - Testing

 If you've been following along, I'm in the process of moving a library of Excel VBA scripts over to Google App Script (specifically Sheets). I've now successfully converted one of my key functions from VBA to App Script.

The function itself is relatively basic - it's a version of VLOOKUP on steroids. Instead of returning a single matching item, it will return a delimited list of matching items. This is handy when you're trying to map a 1:many relationship. If you grok SQL, it's equivalent to:

SELECT ROWID FROM (some range) WHERE fieldName like "%pattern%"

If someone finds it interesting / useful, let me know and I can share it.

Once converted Google makes a very handy little "Debug" function available... except that you cannot actually use it for debugging code that's fully dynamic. I should not that it works if you aren't trying to pass in parameters from a spreadsheet. Maybe this is why everyone writes using code like "SpreadsheetApp.getActiveSheet().getRange(2,2).getValues()"

VBA doesn't have this issue since you can set a breakpoint and the system will break when called with the stack from the caller. For instance, in Excel Sheet1 cell A1 you have "=myFx(A2,B2:B100)", when the breakpoint hits your stack will be the contents from that call. It makes it trivial to debug the function using real world test data.

In App Script, you'll be stuck writing a test function. That's not necessarily bad but it does require you to plan things out a bit more since you're Spreadsheet has to be loaded, the ActiveSheet has to be on the right tab, etc. You could use the named Sheet array but my initial concept on this was to develop and deploy this as a library so I was trying to avoid fixed references. This hasn't worked out so well in practice but more on this later.

As a convention, I've started now using fx prefixes for the functions and test_ for test code. Eventually, I'll probably build a unit test around this so I can monitor the code quality easier. I haven't seen a great way to do this yet but I'll look into in the future.

Example test routine:

function test_myFx()
{
  Logger.log("Test")
  return myFx("U01",SpreadsheetApp.getActiveSheet().getRange(2,2).getValues())
}

function myFx(findStr, findRange)
{
//Do some work
}

Using this pattern, you can place a breakpoint either in the test_myFx or in myFx and use the inline debugger. Note my previous comments about the difference between ranges and values. findRange is actually an array of values. You could pass in the range as:

SpreadsheetApp.getActiveSheet().getRange(2,2)

and findRange would then be represented as a range rather than the array of values; however, Sheets will pass you an array of values when you call the function in a cell. I highly recommend passing the values from the test routine to avoid success in the debugger and failure when using the function.

Once a test routine is implemented, test from the test routine and all of the variables in the debugger will stop saying "Undefined". They should now represent what you've passed from the test routine to the function.

The debugger itself is a standard step into / step over / run / stop so not much surprise there.

The Logger function actually was a very slick feature VBA doesn't really offer. Note that you can't use sprintf type outputs or even the embedded

  Logger.log("Test myV=${myV}")

Instead, it's basic addition operators without formatting... but it does it's job.

  Logger.log("Test myV="+myV)

Note that you can pull from the Utilities.format function for value conversion. I didn't bother since everything I needed was text but it may be useful for some.


Comments

Popular posts from this blog

SMTP Mail and Indy (again)

Having spent a lot of time recently working on a ping scanner using Indy, I noticed that there's a lot of questions still on using SMTP with Indy. Let me first say that I am not an Indy expert. I get along with it successfully but find I still have to research things frequently. With the disclaimer out of the way, we can get to the offering. A while back I wrote a handy little unit to simply send a mail message with or without attachments and with or without providing authentication. It even has support for OpenSSL. I use this unit in a number of applications and have seen it successfully send hundreds of e-mails without issue. I recently added support for threaded message sending to take advantage of the multi-core system I'm now running on. This code has had a few additions (like the logger) that I've gleaned over time from various newsgroup postings, but I didn't record the authors so let me credit the anonymous Internet authors who support Indy. It's really amaz

Detecting a virtualized environment

CubicDesign on delphi-talk.elists.org recently asked the question: "How do I know/detect if my software is running under Windows [or a virtual environment]?" Well, it turns out that it's a lot harder to tell than you would think. Apparently, the VM (VMware, Xen, Wine, etc.) doesn't really want you to be able to do this. At least not easily. For VMware, there is a decent little C routine called jerry.c that does the trick. Jerry actually uses a simple communication channel that VMware left open. It's not 100% foolproof since the admin can change the channel, but that's not likely going to happen unless the system is specifically designed to be a honeypot. If you're running on a honeypot and still have a legitimate reason for detection, you could look at the much more complex scoopy implementation which inspects how the system is actually virtualized using the SIDT CPU instruction instead of a communication channel. Another reference (red pill) is here . F

Vista UAC Manifest

Here's a couple of interesting little tidbits for those using Delphi 2007 and trying to create manifest files to require your program to run as Administrator. For those who don't know what this means, we're talking about running a program and receiving the wonderful little pop-up "A program is trying to take over your life"... well, something like that anyway. You will need your program to receive elevated privileges if you want to do certain things. In my particular instance, I wanted to write to the HKEY_CLASSES_ROOT registry entries. This isn't supposed to be a full reference to what a manifest is, how it works, etc., etc. Microsoft has documented the UAC in some depth and I recommend using their documentation if you want to really understand your options. First, the manifest structure is basically just an XML document that tells Vista how it should handle the program. For elevated privileges, it should look like: < assembly xmlns ="urn:schemas-mic

Copyright 2008-2022, Marshall Fryman