Where communities thrive

  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
    bruce mcpherson
    So anyway, heres a gitter chat associated with the desktop liberation community
    Tony Hine
    bruce mcpherson
    Hi tony
    Tony Hine
    This looks Good!
    bruce mcpherson
    Well let’s see. We haven’t had that many people moving over from g+ yet
    Tony Hine
    People don't like change!
    bruce mcpherson
    Yep.. but in this case we don’t have a choice. Anyway, thanks for joining. Take care
    Let's give it a shot. This looks nice, and might turn out to be a good alternative for YouKnowWhat. Well found, btw, this platform!
    Raul Flores, Jr. MBA "rudy"

    Cool find!

    Open minded. Thanks.

    MarkDown too

    • promising
    • it is

    Test image hosted elsewhere

    let thisBeOk = 'It would seem so';
    bruce mcpherson
    Here's a little snippet I added today - brucemcpherson/desktopliberation#13 for dealing with plucking values from a nested object that may have some missing properties on the way
    Roman Petrochenkov
    Hey there! I'm wondering if anybody can help: I'm looking for plugin for sublime or atom to work with google ads scripts offline an see auto-filling and avaliable objects properties. Does this thing exist?
    bruce mcpherson
    A snippet I added today
    Hi @brucemcpherson , any chance to get asnwer to this, please? https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!topic/google-apps-script-community/_GJDUqR3law
    thanks in advance,


    Hello Bruce! Hello Guys!

    First, thank you very much for your great website!

    I'm trying to use sheetsApi.xlsm


    And everything worked fine until I used a spreadsheet with more data (2.672 cells with data). Then the following error happened:

    Run-time error '13' Type mismatch

    Module: usefulSheetsV4Api / Function putStuffToSheets

    Line: Set result = sheetAccess.setValues(.UsedRange.value, .NAME, .UsedRange.Address)

    Can anybody help me? I really appreciate!

    Thank you!

    Bruce McPherson 🇪🇺🇫🇷🏴󠁧󠁢󠁳󠁣󠁴󠁿
    @filipknapcik @filipknapcik

    so there's a a few syntactical problems with some v8 stuff in the underlying libraries (v8 has some names which are now reserved that clashed with my variable names), which ive now dedealtat with - make sure you're including the latest libraries

    And yes you can update to a sheet (Not a great solution really, but ok for starting off).
    Here's an example of various operations.
    function test_dbAbstraction() {

    // open spreadsheet as database
    const handler = new cDbAbstraction.DbAbstraction (cDriverSheet, {
    siloid:'large airports',
    // since I'm not the only one updating this - these options are good for testing phase
    locking : cDbAbstraction.ENUMS.LOCKING.DISABLED,
    optout: true,
    disablecache: true
    if (!handler.isHappy()) throw 'unable to open sheet';

    // do a query
    const result = handler.query ({iso_country:'UA'});

    // for reference, returns these properties
    // [handleCode, handleError, data, handleVersion, driverVersion, table, dbId, keyProperty, handleKeys]
    if (result.handleCode < 0) throw result.handleError;

    // add some new records - just copy and change the country to something else and write new records
    const saved = handler.save(result.data.map(ob=>({
    if (saved.handleCode < 0) throw saved.handleError;

    // do a query and sort and limit - pick out 10 highest airports in southern hemisphere,
    // and sort in descending order by height
    const limit = handler.query({
    'latitude_deg': handler.constraints([[cDbAbstraction.ENUMS.CONSTRAINTS.LT,0]])
    if (limit.handleCode < 0) throw limit.handleError;

    // find the records we added earlier
    const updated = handler.query({iso_country:'xxxx'})
    if (updated.handleCode < 0) throw updated.handleError;

    // update them to something else
    const update = handler.update( updated.handleKeys, updated.data.map (ob=>({
    iso_country: 'yyyy'
    if (update.handleCode < 0) throw update.handleError;

    // clean up all that
    const removed = handler.remove({iso_country: 'yyyy'})
    if (removed.handleCode < 0) throw removed.handleError;

    @coder777111 I think we can close this now, resolved after our email exchange
    Thank you everybody this is resolved! The problem was caused by an error variable inside a cell like: #NUM. Thank you very much @brucemcpherson !

    Hi Bruce, I am having an issue with doing update method using GAS as DB. Here is my small code:

    // do a query and update record
    var result = handler.query ({ID:'A'},undefined,1,true);
    if (result.handleCode < 0) throw result.handleError;
    var result2 = handler.update( result.handleKeys, result.data.map (function(d) {d.NEW = 'TEST'; return d; }));
    if (result2.handleCode < 0) throw result2.handleError;

    My sheet does get updated with the new value but I see the following error on GAS.

    "Error:TypeError: Cannot read property 'length' of undefined\n at errorStack (Code:258:11)\n at Object.rateLimitExpBackoff (Code:245:15)" (line 274, file "Code")

    Thanks for you awesome support !!!


    Hi Bruce - I used your excellent SheetsApi to to send data from Excel To Googlesheet. It works great. Thanks. I would like to ADD to the Tab in Googlesheet rather than write over it. Grateful if you could suggest how to do it.
    How do I change?

    Private Function writeToSheets(data As cJobject, Optional overwrite As Boolean = False)
    Dim job As cJobject, a As Variant, d As Variant,
    sheetName As String, sheet As Worksheet, ov As Boolean,

    values As cJobject, jor As cJobject, joc As cJobject

    For Each job In data.children
        a = Split(LCase(job.toString("range")), "!")
        sheetName = CStr(a(0))
        '// see if the sheet exists
        Set sheet = sheetExists(sheetName)
        If (isSomething(sheet)) Then
            If (Not overwrite) Then
                ov = MsgBox("sheet already exists " & sheetName & " overwrite?", vbYesNo)
            End If
            If (ov Or overwrite) Then
                Set sheet = Nothing
            End If
            Set sheet = sheets.add
            sheet.NAME = sheetName
        End If
        '// now write the data
        If (isSomething(sheet)) Then
            Set values = job.child("values")
            'we have to dimension to the maximum size of the array, as empty rows can return jagged arrays
            Dim m As Long
            m = 0
            For Each jor In values.children
                If jor.hasChildren Then
                    If (jor.children.Count > m) Then
                        m = jor.children.Count
                    End If
                End If
            Next jor
            If (m > 0) Then
                ReDim d(0 To values.children.Count - 1, 0 To m)
                For Each jor In values.children
                    For Each joc In jor.children
                        d(jor.childIndex - 1, joc.childIndex - 1) = joc.value
                    Next joc
                Next jor
            '// now we just need to write it out
                sheet.Cells(1, 1) _
                .Resize(values.children.Count, m) _
                .value = d
            End If
        End If
    Next job

    End Function ' check if this childExists in current children

    Raj B
    @brucemcpherson hi Bruce I am trying to use your great routine https://ramblings.mcpher.com/integratingappswithotherplatforms/googlesheetsv4apifromvba/#More to push data from Excel to Googlesheets - but rather than clear and overwrite the googlesheet, I need to append the data. I tried to use the Method: spreadsheets.values.append but couldnt do it. Any tips?
    Matt Jackson
    Hi Bruce until recently I had been successfully using your implementation of vbagit. However it seems GIT decided to up and depreciate something related to oauth which now breaks things. I battled through porting your code to work within a word environment as opposed to excel however not sure on where start with this on. I thought I might ask it you or someone else may have run into this issue or found a workaround. I Feel your solution to git via VBA is the best I have been able to find to date. Great work. Also your JSON and cjObject implementations are awesome.!!! :)
    Bruce McPherson 🇪🇺🇫🇷🏴󠁧󠁢󠁳󠁣󠁴󠁿
    Hi Matt - what's broken in the github oauth. I don't even have a windows machine any more so i'm not sure if i can help with the VBA. However I still use the github api quite a bit from other projects and i havent noticed any breaking changes.
    Matt Jackson
    according to a notification i receive from github the oauth connector has been depreciated. Now when I do a commit github sends me an email notifying that the endpoint is no longer valid. Will report back to see if I can get a more detailed error.
    Hi! Many thanks for your great solution! It's worked from the scratch with Win 10 and Ms Excel 2016, but I received the parameter error on Win 7 and Ms Excel 10 after Browser API access validation. So I've debug the code and found the solution. At cBrowser Class Module function httpPost the string ohttp.send data - should look like ohttp.send (data) - add parathensis and the problem was gone. See more at (https://stackoverflow.com/questions/308826/msxml2-xmlhttp-send-method-works-with-early-binding-fails-with-late-binding)