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 1

As a long time (unfortunately read "old") user of Office products, I've done a lot of work in VBA to make the products more functional. I've been using Google Sheets sporadically as well, but never got around to using App Script until now. In the process of getting familiar with it, I found the information was widely spread and not very concise. Below are my observations for other would-be converters of VBA to App Script.

Input parameters are a bit different from Excel VBA. You don’t get ranges but realized arrays of values. This is actually how VBA treats the range declaration but Google Sheets has ranges and arrays which throws the nomenclature just a bit. In the end, it’s passed the same, just watch for the term difference between the systems. 

For instance, in VBA, the function:

Function myFx(findStr As String, r As Range)

Dim X As Long, found As Long

found = 0

For X = 1 To r.Count

   If r(X).Value = findStr Then

      found = found + 1

   End If

Result = found


Is effectively identical (except for language differences and 1-based vs 0-based arrays) in App Script:


 * Counts the number of times findStr is found in the array r.


 * @param {string} findStr The string to find.

 * @param {array} r The array to search.

 * @return The number of times findStr was found in the array r.

 * @customfunction


function myFx(findStr, r) {

  var found = 0;

  for (let x = 0; x < r.length; x++) {

    if (r[x] == findStr) found += 1;


  return found;


JavaScript of course offers a lot of different ways to write that loop, but as a direct replacement, it’s not terribly far from where things started. Also note that there's nothing that defines the system as a one-dimensional array. You may be better off with a more flexible structure such as:

function myFx(findStr, r) {
  var found = 0;
  r.forEach( function(row) {
     function(col) {
        if (col == findStr) found += 1;
  return found;

The only time you care about the difference between range and value is if your App Script is pulling ranges directly from some Sheet. If you do this, make sure you convert the range to a value array. I’ve seen lots of people doing this in their examples but don’t understand why you’d write a one-off, statically linked App Script vs making it dynamic. Maybe I just haven’t gotten to that need yet.

Getting the function to show help / auto-complete:

This is done via a JsDoc compliant header block. From Google's example for a custom function called DOUBLE, the block looks like:


 * Multiplies the input value by 2.


 * @param {number} input The value to multiply.

 * @return The input multiplied by 2.

 * @customfunction


function DOUBLE(input) {

  return input * 2;


A couple of clarifications here:

  1. The format is finicky.

    1. You need the /** header. 

    2. You need the @cutomfunction at the end (no, not YOUR custom function name, but literally @customfunction). 

    3. You need * on each line

    4. You need the */ end

  2. The params are defined specifically. Google’s example only shows one input but you can do complex inputs such as:

* @param {string} in1 Input string 1 that does something

* @param {array} in2 Input array 1 to do something

* @param {array} in3 Input array 3 to do something else

Hopefully this helps someone.


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