Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    bruce mcpherson
    @brucemcpherson
    #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

    Raj B
    @RajB93957057_twitter
    @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
    @the.jackal_gitlab
    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 πŸ‡ͺπŸ‡ΊπŸ‡«πŸ‡·πŸ΄σ §σ ’σ ³σ £σ ΄σ Ώ
    @brucemcpherson_twitter
    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
    @the.jackal_gitlab
    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.
    Andrey
    @AndreyAtGitHub
    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)
    Steve Bolman
    @ByEqualation
    I was just watching the excellent "TU13 Highlight: Google Sheets Developer Metadata" (link below) and specifically @5.24 there is an excellent tip about copying all of the "Documentation" into the script file. I was just wondering if anyone knows if this documentation file is posted somewhere formally on GitHub or elsewhere? https://www.youtube.com/watch?v=_c57bDfwcAE
    bruce mcpherson
    @brucemcpherson
    The documentation is here
    Steve Bolman
    @ByEqualation
    Many thanks
    Steve Bolman
    @ByEqualation
    Actually I found this link to your excellent "Sheets Meta Data Advanced service library utilities" .gs file. I was very keen to see how you had implemented "getIntersection" but all the other functions and learnings that you provide are tremendous. Again Many thanks for all your efforts and support.
    https://script.google.com/home/projects/1wYuya4Vchqa8Ovl0MYSxYwKM8ZjiGFqO5NUo_KzGi-GPMT-HfDEJ6i3k/edit
    bruce mcpherson
    @brucemcpherson
    ok - presumably you have all you need now?
    Steve Bolman
    @ByEqualation
    Yes Bruce I really appreciate the information and resources you provide. Many thanks.
    thuanrua88
    @thuanrua88
    image.png
    Hi @brucemcpherson , i have problem with file : https://ramblings.mcpher.com/integratingappswithotherplatforms/googlesheetsv4apifromvba/ my problem is : I can't run function sheetsOnceOff(), it's show message even though I declared
    Sergey
    @sergeos
    dear coders, can you help me in vba with code to upload excel sheet?
    Sergey
    @sergeos
    while sub sheetsOnceOff() running then code call internet explorer for get/set credentials. And then IE shows message what google cannot supplying old browser. And sub shows error.
    Samphan Raruenrom
    @untsamphan

    My team needs to upload large files to a common Google Drive. I've tried a few available codes but found them inadequate. So I wrote this small library (100 lines typescript) based on several other codes, to do the job. I've just finished it today. It is not very well tested yet.

    I've published it in github so anyone interested can play with it.
    https://github.com/untsamphan/gdrive-upload

    corto-72
    @corto-72
    Hi @brucemcpherson first of all thanks for all your knowledge sharing , this is amazing info!
    I am using your exemple of using GAS & vue/vuex/vuetify to try and learn from this.
    But I am struggling with the usage of external js modules....
    In your exemple I'd like to "enhance" the transfer status tool tip with the date of latest update, using DayJs library, importing it rather than adding it to the cdn file. (May be this is where is my mistake , but I wanted to take same approach as for Qottle/TimeSimmer...)
    I declared the source in the index.html file (Include.mjs section).
    In main.js file , I added "Store.addComponent(modules.DayJs)" just after "Store.addComponent(modules.CountryFlag)"...
    But then I am not sure how to call it in the the vueTransfer.vue file .... Can you help ? thanks in advance!!!
    corto-72
    @corto-72
    Think I just realised my first mistake... addComponent is to add Vue component, not libraries...
    corto-72
    @corto-72
    Finally reverted to using CDN file to declare globaly DayJs, easier this way....
    dialdhruv
    @dialdhruv
    i was using script to put data to google sheet but now i'm facing problem.
    when i run sheetOnceoff internet explorer will popup and then after EDGE browser will open automatically with google login page after doing login and authorization i got the last page of the browser like this . now where i paste this code?
    dialdhruv
    @dialdhruv
    image.png
    GerritB1966
    @GerritB1966

    I had it working for several years, thanks for that.
    Now I wanted to add another fellow worker to use the same sheets.
    Now nothing is working anymore.
    So I started with new API, new OAuth 2.0.
    When running sheetsOnceOff() I receive message below:

    Sign in or provide access to Excel2GoogleSheets
    To sign in or provide access:

    1. Copy the authorisation code from the authorisation code section.
    2. Navigate to Excel2GoogleSheets.
    3. Paste the authorisation code on the Excel2GoogleSheets screen.
      4/1AX_JDlM
      Please copy this code, switch to your application and paste it there:
      Where can I add these Authorisation Code?
    Andrey
    @AndreyAtGitHub

    When running sheetsOnceOff() I receive message below:

    Sign in or provide access to Excel2GoogleSheets
    To sign in or provide access:

    1. Copy the authorisation code from the authorisation code section.
    2. Navigate to Excel2GoogleSheets.
    3. Paste the authorisation code on the Excel2GoogleSheets screen.
      4/1AX_JDlM
      Please copy this code, switch to your application and paste it there:
      Where can I add these Authorisation Code?

    I've commented the code which copy Auth Code from browsers in CBrowser class and add the input box, to add manually.

    Public Function Navigate(fn As String, Optional lockDown As Boolean = False, _
    Optional visible As Boolean = True) As cBrowser
    ' bring up the web page requested
    pHtml = fn
    pLockdown = lockDown
    pSuccessCode = vbNullString
    pDeniedCode = vbNullString
    With browser
    If lockDown Then
    .AddressBar = False
    .MenuBar = False
    .Resizable = False
    End If
    .visible = visible
    .Navigate2 pHtml
    pLockActive = True
    ' will fire document complete, then we can set this off
    'Do
    ' DoEvents
    ' If Not pLockdown Then
    ' pLockActive = Not (.readyState = READYSTATE_COMPLETE And Not .Busy)
    ' End If
    ' Loop Until Not pLockActive
    End With
        pSuccessCode = InputBox("Please Put the Auth code from Browser")
        pLockActive = False
    Set Navigate = Me
    End Function
    GerritB1966
    @GerritB1966
    @AndreyAtGitHub Thanks, it's working great!
    Gabriele Pigani
    @spudboy71_gitlab
    Hi Bruce, I am a newbie and I have a question related to the topic of chapter 10 of the book "Going GAS".
    About showModalDialog or showSidebar I have a question that I can’t solve: how suspend (if it is possible) the server-side script while the showModalDialog or showSidebar is open?
    Basically I would like the script resumes after the user dismisses the dialog.
    Sorry for my bad English I hope I made myself clear.
    Thanks bye
    Gabriele
    GerritB1966
    @GerritB1966
    @AndreyAtGitHub ,
    The code is running perfectly when I start it manually. Would it be possible to run this by Windows Task scheduler. When I run it by Task scheduler it will not do the update to Google sheets.
    Bruce McPherson πŸ‡ͺπŸ‡ΊπŸ‡«πŸ‡·πŸ΄σ §σ ’σ ³σ £σ ΄σ Ώ
    @brucemcpherson_twitter
    @spudboy71_gitlab Hi Gabriele. If you wanted to interact with server side from the dialog, you'd use google.script.run to communicate. One way would be to have a server side function that could write and read to cacheservice or propertyservice that would be interrogated periodically by your running server side code and would stop and start depending on some value you'd written there. The client side would call that server side function using google.script.run to set the same cache/property entry to some value that would mean to stop/start
    @AndreyAtGitHub Good solution - thank you for this. If you'd like, I can post this on my site and credit you with the solution - if you'd like me to do this, please write a short post and I'll publish it.
    @corto-72 yes - that's the simplest - Apps Script doesn't support modules.
    Bruce McPherson πŸ‡ͺπŸ‡ΊπŸ‡«πŸ‡·πŸ΄σ §σ ’σ ³σ £σ ΄σ Ώ
    @brucemcpherson_twitter
    Please see solution in this thread by @AndreyAtGitHub @sergeos @thuanrua88
    Bruce McPherson πŸ‡ͺπŸ‡ΊπŸ‡«πŸ‡·πŸ΄σ §σ ’σ ³σ £σ ΄σ Ώ
    @brucemcpherson_twitter
    1000 pages and counting
    Most years I do a post on β€˜a year in Apps Script’, looking back over the changes in the platform over the preceding year. As many of you will probably know, I’ve been running this web site for a long time now – I can’t find the actual date as I’ve changed platform a couple of times – but it was about 12 years ago. Now that I’ve exceeded 1000 pages of content (to be fair some of it is pretty outdated nowadays – and some of it was pretty poor in the first place), I figured that instead of covering Apps Script specificaly I’d be a little self indulgent this year and do a retrospective of this site and it’s evolution.
    Gabriele Pigani
    @spudboy71_gitlab
    Hi Bruce, thanks for your kind reply but for my skills what you describe is too difficult to accomplish. If it can be useful, besides me, also other users and in the future you will want to give an example of application, I would be grateful.
    Thanks bye
    Bruce McPherson πŸ‡ͺπŸ‡ΊπŸ‡«πŸ‡·πŸ΄σ §σ ’σ ³σ £σ ΄σ Ώ
    @brucemcpherson_twitter
    @spudboy71_gitlab Perhaps it's best if you describe what you want to achieve. Typically when a dialog box or sidebar is shown, there's nothing running server side anyay.
    Gabriele Pigani
    @spudboy71_gitlab
    I am automating the data entry of a rollout table that I use at work. Starting from the first value manually inserted in the table, I would like to insert the following values one by one according to a predetermined scheme (guided). e.g.: first value manually inserted in cell A1 second value selected from dropdown list and inserted in cell A5 third value selected from dropdown list and inserted in cell A3 fourth value selected from dropdown list and inserted in cell A10 etc. until the end of the insertion.
    I can make everything work with the onEdit(e) trigger if I create the dropdown lists manually using the data validation directly in the cell, but if I want to use a dialog box with dropdown lists it is complicated, because the onEdit(e) trigger does not work and therefore I can not perform the insertion according to the default scheme that I described before as an example A1 -> A5 -> A3 -> A10 etc..
    Bruce I absolutely do not want to waste your time, if there is a solution to what I described above that may have a utility for other users ok, otherwise it does not matter and I thank you anyway for the answers you gave me.
    bye Gabriele
    Bruce McPherson πŸ‡ͺπŸ‡ΊπŸ‡«πŸ‡·πŸ΄σ §σ ’σ ³σ £σ ΄σ Ώ
    @brucemcpherson_twitter
    @spudboy71_gitlab If you want to send me what you've done so far, I can have a quick look at it if you want. my email is bruce@mcpher.com
    CivilEng Spreadsheets
    @CivilEngSpread_twitter
    Hi Bruce, been searching for quite sometime about this google sheets and excel vba integration. I have a problem with the "sheetsOnceOff" function. I have created the OAuth 2.0 client ID and client secret and entered it in the "sheetsOnceOff" function. After running it, I'm getting a message in excel "Failed to authorize to google scope sheets: denied code" and my internet explorer browser pops out asking for google account login and when I login it gives me an "Error 403:access_denied". Where should I fix this problem to get this from working?
    Predrog
    @Predrog
    Hi there
    @brucemcpherson_twitter There is workaround to that problem, the internet explorer cannot be used to OAuth 2.0?
    I got "Error (400): invalid_request"