Ask a Question
Back to All


Can someone please help me. I need just the oauth2 steps to add to the start of my excel power queries as now 2 factor has been implemented they no longer work with just basic private log in.

This below is what I have so far and it returns the jobs in the power query window but will not upload to worksheet as the down load has discontinued due to this error in the token response:

DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details: DataSourceKind=Web DataSourcePath=https://api.servicem8.com/oauth/authorize

And this error at end of query: 'We couldn't authenticate with the credentials provided. Please try again.'


// Get the API Token
api_url = "https://api.servicem8.com/",
token_path = "oauth/authorize",
ClientID = "588360",
Secret = "0874cda820434972bf572069342b53db",

 EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),

Token_Response  = Json.Document(Web.Contents(api_url,
  RelativePath = token_path,
  Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],

// Get the token from the API response 
token = Token_Response[access_token],

// Query the API for Transactions between startDate and endDate and return all fields
path = "api_1.0/job",
endDate = "2023-09-17 00:00:00",
startDate= "2023-09-11 23:59:59",
fields = "all",

data= Json.Document(Web.Contents("https://api.servicem8.com/api_1.0/job.json?%24filter=active%20eq%20%271%27")),

#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"uuid", "date", "job_address", "work_done_description", "generated_job_id", "completion_date", "company_uuid", "payment_processed", "total_invoice_amount", "purchase_order_number", "status"}, {"Column1.uuid", "Column1.date", "Column1.job_address", "Column1.work_done_description", "Column1.generated_job_id", "Column1.completion_date", "Column1.company_uuid", "Column1.payment_processed", "Column1.total_invoice_amount", "Column1.purchase_order_number", "Column1.status"})

#"Expanded Column1"