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