I posted how to include and prettify snippets from GAS, Gist, and scriptDB the other day, adding the ability to select particular functions from within modules. See the Excel liberation site for more detail. With Google Apps Script, we only had to worry about how to pick out functions from a javascript module. Now that Gists are supported – well, they could be any language. Turns out though that it’s pretty straightforward to add new languages. Here’s an example of selecting a couple of functions from a module of many VBA functions, stored in a gist.
https://storage.googleapis.com/toasty/p/gaspubcontainer.html?source=gist&library=3623968&func=swCleanSheet,swGetTables
which gives us the code snippet below.
Private Function swCleanSheet(job As cJobject, ws As String) As cDataSet ' put headers to a clean sheet Dim ds As New cDataSet, cj As cJobject, r As Range Set r = firstCell(wholeSheet(ws)) r.Worksheet.Cells.ClearContents ' these are the headings If job.children.count > 0 Then For Each cj In job.children r.Offset(, cj.childIndex - 1).value = cj.key Next cj ' create a data set Set swCleanSheet = ds.populateData(r.Resize(1, job.children.count)) End If End Function Private Function swGetTables(shortName As String) As cRest Const tableDirectory = "SELECT name FROM sqlite_master " & _ "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' " & _ "Union all " & _ "SELECT name FROM sqlite_temp_master " & _ "WHERE type IN ('table','view') " & _ "ORDER BY 1" ' lets see if we can get the tables that exist in this shaperwiki Set swGetTables = restQuery(, "scraperwikidata", _ shortName & "&query=" & tableDirectory, , , , , False) End Function
Here’s the code that figures out where the functions start and finish. (This time we’re showing some javascript hosted in a Google Apps Script shared library)
http://xliberation.com/p/gaspubcontainer.html?source=script&library=mcpher&module=gasPub&func=getGist,getFunctions,getFunction,getFunPos
function getGist(e) { var fn = "https://gist.github.com/raw/" + e.parameter.library + (e.parameter.module ? "/" + e.parameter.module : ""); try { var data = new cBrowser().httpGET ( fn,undefined,undefined,undefined, e.parameter.cache=="true" ); return { data : data } ; } catch (err) { return {error: "failed to get gist at " + fn, data:null }; } } function getFunctions(e,moduleSource) { if (e.parameter.func) { var s = ""; var a = Split(e.parameter.func); for ( var i=0;i<a.length;i++) { var o = getFunction(a[i],moduleSource); if (o.error) return o; s += (s ? "\n" : "" ) + o.data; } return {data: s}; } else return {data: moduleSource}; } function getFunction(functionName, moduleSource) { var fund = getFunPos ( functionName, moduleSource ), f="",pos; if (!fund) return {error : "function " + functionName + " not found in " + moduleSource, data : null}; // we found it pos = fund.pos; switch (fund.language) { case 'js': var depth = undefined; while ( pos < Len(moduleSource) && ( depth || isUndefined(depth) ) ) { var s = Mid (moduleSource, ++pos,1); if (s == "{" ) depth = isUndefined (depth) ? 1 : depth + 1; else if (s== "}") { if (isUndefined(depth)) return {error : "function " + functionName + " invalid {} " + moduleSource, data : null} ; depth --; } f += s; } break; case 'vba': f = Mid(moduleSource, ++pos); var rx = new RegExp("\\nend\\s*?(function|sub|property)","i"); var x = f.search(rx); if (x < 0) return {error : "function " + functionName + " invalid {} " + moduleSource, data : null} ; // length of string is pos end is found at plus length of end f = Mid(f,1,Len(rx.exec(f)[0])+x); break; default: mcpher.DebugAssert (false, "Unknown language " + fund.language); break; } return ( {data : f} ); } function getFunPos(target, input) { var fund = {}; //----try javascript/GAS .. support function x() and c.prototype.x = function() fund = {language: 'js', pos :input.search ( new RegExp( "(function\\s*?" + target + "\\s*?\\()|(\\w.prototype." + target + "\\s*?=\\s*?function\\s*?\\()" ) ) } ; //----try VBA support private/public// function//sub/property if (fund.pos < 0) { var r = "(public|private|\\s*?)\\s+?(function|sub|(property\\s*?(get|let|set|\\s*?)))\\s*?(" + target + "\\s*?\\()" ; fund = { language: 'vba', pos : input.search ( new RegExp(r ,"i") ) } ; } return fund.pos < 0 ? null : fund; }
Challenge for you
Anybody want to contribute appropriate regexes for other languages (or improve the basic ones being used here) to be able to find the beginning and end of functions? See the snippets above for how.