Tricking VBA when updating Power Query

[kalspiros]

Hi all,

I have a spreadsheet where I need the user to add their API Token so that I will get some JSON data.

I do not want them to drop the value in a cell because they might forget about it and save it there.

This is what I have set on VBA as seen on this link https://hatfullofdata.blog/excel-power-query-vba-to-edit-a-parameter-value/ to get the token via a pop-up

Option Explicit

Sub ChangeParameterValue(ParameterName As String, ParameterValue As String)

    Dim qry As WorkbookQuery
    Dim formula As Variant
    
    '=== Get the query
    Set qry = ThisWorkbook.Queries(ParameterName)
    
    '=== Split the formula into 3 parts and update the second one
    formula = Split(qry.formula, Chr(34), 3)
    formula(1) = ParameterValue
    
    '=== Update the parameter value
    qry.formula = Join(formula, Chr(34))
    
End Sub

Sub MySub()
    ChangeParameterValue "api_key", InputBox("Please provide your token", "API Token")
    ActiveWorkbook.RefreshAll
    ChangeParameterValue "api_key", " "
End Sub

Everything works perfectly fine except a tiny hiccup.

As you can see on the final step, I am calling ChangeParameterValue again to reset the token so that it will not be saved in the api_key query.

If I will accidently place a wrong token, the "Access Web content" window will pop up, which make sense:

If I will try immediately after and add a correct token, the "Access Web content" comes back again.

It seems that the macro runs all the way and resets the api_key before the whole ActiveWorkbook.RefreshAll is completed.

If I remove the ChangeParameterValue "api_key", " " everything works fine, but that's something I want to avoid since it will keep the token in the query for further users to see.

FYI, the query that calls JSON, below. api_key as seen above and Start_Date/ End_Date as set by the user in relavent cells.

let
    apiToken = api_key,
    url = "https://************/api/clients/invoices/",
    query = [
        start = Start_Date,
        end = End_Date,
        format = "json"
    ],
    headers = [
        Authorization = "Token " & apiToken,
        #"Content-Type" = "application/json"
    ],
    Source = Json.Document(Web.Contents(url, [Headers=headers, Query = query])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
    #"Coverted to Table"

This problem might have numerous approaches on resolving or it should be better placed on a VBA forum thus, apologies for any possible confusion.

Many thanks for any help!

[lbendlin]

What's the lifespan of that token?  Are you chasing a non-existing problem?

[kalspiros]

Hey @lbendlin thanks for your message! Not much on tokens tbh but my understanding is that they have no expiry date. They stick with a particular user. I am not sure I understood your second query.

[lbendlin]

Tokens normally expire rather fast (an hour or so) and you usually need to get a refresh token.

So if your process stores the initial token, and it expires after an hour - that would mean the stored token can not do any harm and doesn't need to be cleared.