Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kalspiros
Helper I
Helper I

Tricking VBA when updating Power Query

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:

kalspiros_0-1659524633243.png

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!

3 REPLIES 3
lbendlin
Super User
Super User

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

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.

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors