Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
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.