Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    bruce mcpherson
    @brucemcpherson
    So anyway, heres a gitter chat associated with the desktop liberation community
    Tony Hine
    @TonyHine
    Hi
    bruce mcpherson
    @brucemcpherson
    Hi tony
    Tony Hine
    @TonyHine
    This looks Good!
    bruce mcpherson
    @brucemcpherson
    Well let’s see. We haven’t had that many people moving over from g+ yet
    Tony Hine
    @TonyHine
    People don't like change!
    bruce mcpherson
    @brucemcpherson
    Yep.. but in this case we don’t have a choice. Anyway, thanks for joining. Take care
    deMeeuw
    @deMeeuw
    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"
    @rudimusmaximus

    Cool find!

    Open minded. Thanks.

    MarkDown too

    • promising
    • it is

    Test image hosted elsewhere

    let thisBeOk = 'It would seem so';
    bruce mcpherson
    @brucemcpherson
    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
    @rp-goeuro
    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
    @brucemcpherson
    A snippet I added today
    #14
    filipknapcik
    @filipknapcik
    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,
    coder777111
    @coder777111

    https://gitter.im/desktopliberation/community#
    https://github.com/

    Hello Bruce! Hello Guys!

    First, thank you very much for your great website!

    I'm trying to use sheetsApi.xlsm

    https://ramblings.mcpher.com/integrating-apps-with-other-platforms/using-the-google-sheets-v4-api-from-vba-to-integrate-sheets-and-excel/

    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!

    @brucemcpherson
    Bruce McPherson 🇪🇺🇫🇷🏴󠁧󠁢󠁳󠁣󠁴󠁿
    @brucemcpherson_twitter
    @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',
    dbid:'1VuWsLU40660_KEx_ug5safZbfbEInjXs7uQNeHtErnk',
    // 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=>({
    ...ob,
    iso_country:'xxxx'
    })))
    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]])
    },{
    sort:'-elevation_ft',
    limit:10
    })
    if (limit.handleCode < 0) throw limit.handleError;
    console.log(limit.data)

    // 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=>({
    ...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
    coder777111
    @coder777111
    Thank you everybody this is resolved! The problem was caused by an error variable inside a cell like: #NUM. Thank you very much @brucemcpherson !
    johnkelton
    @johnkelton

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

    ddranchi
    @ddranchi

    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
                sheet.Cells.ClearContents
    
            Else
                Set sheet = Nothing
            End If
        Else
            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