Skip to main content

Posts

Showing posts from June, 2022

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...
Post a Comment
Read more

From Excel VBA to Google App Script: Unit Tests

If you've been following along on my Excel VBA App Script journey ( Part 1 , Part 2 ), I'm converting my old Excel VBA scripts to Google App Scripts. I'm specifically focused on Excel to Sheets at the moment but the general process applies to other App Script development. As I mentioned last time, once I figured out that you cannot use the integrated debugger without a wrapper routine, the next logical exercise was adding unit tests. The test wrapper is essentially ready to go as a unit test, it's just a question of how to formalize it a bit. At it's most basic, we could convert the test wrapper to return a pass/fail result and be done with it, ala: function   test_myFx () {    Logger . log ( "Test" )    return  myF x ( "U01" , SpreadsheetApp . getActiveSheet (). getRange ( 2 , 2 ). getValues ()) } function   myFx (findStr, findRange) { //Do some work } converts to: function   test_myFx () {    Logger . log ( "Test" )   if (m...
Post a Comment
Read more

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 " ...
Post a Comment
Read more

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      ...
Post a Comment
Read more

Copyright 2008-2022, Marshall Fryman