With a few months of hacking away and increasing levels of success, I now have a way to import Upwork jobs into SharePoint for further job funnel actions. It wasn't easy.
The key to this process is to first get the key... from Upwork: API Documentation
Note that the API documentation examples are kinda difficult to follow. Also, you need to properly phrase your intent. If you don't, your request for a key will be rejected, with the following warning:
Once you have the key in hand, you will need to create a custom connector in Power Automate: Create a custom connector from scratch | Microsoft Learn. The internals of the connector will look like this in JSON:
{
"swagger": "2.0",
"info": {
"title": "UpWork",
"description": "Connects to Upwork to get job and other information in order to respond to work requests.",
"version": "1.0"
},
"host": "api.upwork.com",
"basePath": "/",
"schemes": [
"https"
],
"paths": {
"/graphql": {
"post": {
"summary": "JobsRequest",
"consumes": [
"application/json"
],
"produces": [
"application/json"
],
"responses": {
"200": {
"description": "successful operation",
"schema": {
"$ref": "#/definitions/GraphQLResponse"
}
}
},
"parameters": [
{
"in": "body",
"name": "body",
"description": "GraphQL query",
"required": true,
"schema": {
"$ref": "#/definitions/GraphQLQuery",
"x-ms-visibility": "advanced"
},
"x-ms-visibility": "advanced"
}
],
"operationId": "JobsRequest"
}
}
},
"definitions": {
"GraphQLQuery": {
"type": "object",
"properties": {
"query": {
"type": "string",
"title": ""
},
"variables": {
"type": "string",
"title": ""
}
}
},
"GraphQLResponse": {
"type": "object",
"properties": {
"data": {
"type": "object"
}
}
}
},
"securityDefinitions": {
"oauth2-auth": {
"type": "oauth2",
"flow": "accessCode",
"authorizationUrl": "https://www.upwork.com/ab/account-security/oauth2/authorize",
"tokenUrl": "https://www.upwork.com/api/v3/oauth2/token",
"scopes": {}
}
},
"security": [
{
"oauth2-auth": []
}
],
"tags": []
}Note: If you can't find the link in Power Automate to Custom Connectors, go to the link for Connectors and then add /custom at the end, so the URL looks like this:
https://make.powerautomate.com/environments/Default-XXXXX-XXXX-XXX-XXX-XXXXXXXXXXX/connections/custom
Your Client ID and Client Secret will be provided by Upwork. You will also need a redirect URL to get things going. I created one in Azure that looks something like this:
https://global.consent.azure-apim.net/redirect/upwork-{alphanumeric gobbledygook}
Requests and responses are in the form of GraphQL.
Once you have your API. you can make requests (in my case, to find jobs). Other kind of requests are also available and spelled out in the Upwork API documentation.
Flow
In my case, I set up the flow to occur every hour. You can do more often, however please note that after much optimization my flow takes 21 minutes or so to run.
The request needs to include the GraphQL query and a FilterStatement.
The query looks something like this:
fragment MarketplaceJobpostingSearchEdgeFragment on MarketplaceJobpostingSearchEdge {node {id title description experienceLevel client {location {city country timezone}} occupations {category {id prefLabel}} preferredFreelancerLocation skills {name prettyName highlighted} publishedDateTime ciphertext recordNumber amount {currency rawValue} hourlyBudgetType hourlyBudgetMin {currency rawValue} hourlyBudgetMax {currency rawValue} totalApplicants}}
fragment PageInfoFragment on PageInfo {endCursor hasNextPage}
query marketplaceJobPostingsSearch(
$marketPlaceJobFilter: MarketplaceJobPostingsSearchFilter,
$searchType: MarketplaceJobPostingSearchType,
$sortAttributes: [MarketplaceJobPostingSearchSortAttribute]
) {
marketplaceJobPostingsSearch(
marketPlaceJobFilter: $marketPlaceJobFilter,
searchType: $searchType,
sortAttributes: $sortAttributes
) {
totalCount
edges {
...MarketplaceJobpostingSearchEdgeFragment
}
pageInfo {
...PageInfoFragment
}
}
}The filter statement can look like this:
{
"marketPlaceJobFilter": {
"locations_any": ["United States","Canada"],
"skillExpression_eq":"accounts-payable,accuracy-verification,adaptive-web-design,administrative-support,adobe-illustrator,agile-software-development,ai-generated-code,ai-model-integration,amazon-ec2,amazon-web-services,analytics-dashboard,apache-http-server,api,api-developm,api-development,api-integration,app-development,article-writing,automation,aws-application,aws-cloudformation,aws-lambda,back-end-development,blog-article,blog-writing,brochure,business-plans,c,c#,c++,company-research,competitive-analysis,crm,css,css3,customer-satisfaction,customer-service,customer-support,custom-php,data-analysis,database,database-architecture,database-development,database-management,data-mining,data-science,desktop-applications,drop-shipping,email-communication,email-support,english,error-detection,executive,experiment-design,figma,file-management,financial-analysis,financial-modeling,financial-projections,frontend-development,full-stack-development,git,graphql,java,javascript,jquery,json,linux-system-administration,list-building,macros,map-integration,marketing-research,microsoft-project,microsoft-access,microsoft-access-programming,microsoft-excel,milestones,mongodb,ms-sql,next.js,node.js,pdf-conversion,performance-monitoring,php,plugin-development,postgresql,progressive-web-apps,project-scheduling,project-management-capability,python,python-script,react-js,research-documentation,rest-api,restful-api,salesforce.com,scheduling,software-design,software-testing,source-file,spreadsh,spreadsheets,sql,tableau,typescript,very-small-1-9-employees,visual-basic-for-applications,web3,web-programming,website,website-asset,website-redesign,website-security,web-testing,windows,wire-framing,wordpress,wordpress-plugin,wordpress-website,word-processing,writing,xml",
"searchType": "USER_JOBS_SEARCH",
"sortAttributes": [
{"field": "RECENCY"}
]
}After retrieving a cursor value in the response and handling the response, I then loop back and update the filter to add the cursor since the returns are paginated:
{
"marketPlaceJobFilter": {
"locations_any": ["United States","Canada","United Kingdom"],
"skillExpression_eq":"accounts-payable,accuracy-verification,adaptive-web-design,administrative-support,adobe-illustrator,agile-software-development,ai-generated-code,ai-model-integration,amazon-ec2,amazon-web-services,analytics-dashboard,apache-http-server,api,api-developm,api-development,api-integration,app-development,article-writing,automation,aws-application,aws-cloudformation,aws-lambda,back-end-development,blog-article,blog-writing,brochure,business-plans,c,c#,c++,company-research,competitive-analysis,crm,css,css3,customer-satisfaction,customer-service,customer-support,custom-php,data-analysis,database,database-architecture,database-development,database-management,data-mining,data-science,desktop-applications,drop-shipping,email-communication,email-support,english,error-detection,executive,experiment-design,figma,file-management,financial-analysis,financial-modeling,financial-projections,frontend-development,full-stack-development,git,graphql,java,javascript,jquery,json,linux-system-administration,list-building,macros,map-integration,marketing-research,microsoft-project,microsoft-access,microsoft-access-programming,microsoft-excel,milestones,mongodb,ms-sql,next.js,node.js,pdf-conversion,performance-monitoring,php,plugin-development,postgresql,progressive-web-apps,project-scheduling,project-management-capability,python,python-script,react-js,research-documentation,rest-api,restful-api,salesforce.com,scheduling,software-design,software-testing,source-file,spreadsh,spreadsheets,sql,tableau,typescript,very-small-1-9-employees,visual-basic-for-applications,web3,web-programming,website,website-asset,website-redesign,website-security,web-testing,windows,wire-framing,wordpress,wordpress-plugin,wordpress-website,word-processing,writing,xml",
"pagination_eq": {"after":"@{outputs('End_Cursor')}","first":50}
},
"searchType": "USER_JOBS_SEARCH",
"sortAttributes": [
{"field": "RECENCY"}
]
}Note, you will need to update the skills list to match your needs. You can see the skill codes in the responses. Also, you can't filter by exception. I filter by retrieving as much as I can, then using a skills list in SharePoint to check for exceptions and then skip the job.
The jobs that are returned from the request are best looped through and processed individually, I do two stabs at the JSON - First to get the whole response and then after parsing out each item within a loop. The first JSON translation schema looks like this:
{
"type": "object",
"properties": {
"data": {
"type": "object",
"properties": {
"marketplaceJobPostingsSearch": {
"type": "object",
"properties": {
"totalCount": {
"type": "integer"
},
"edges": {
"type": [
"array",
"null"
],
"items": {
"type": [
"object",
"null"
],
"properties": {
"node": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"title": {
"type": "string"
},
"description": {
"type": "string"
},
"experienceLevel": {
"type": [
"string",
"null"
]
},
"client": {
"type": "object",
"properties": {
"location": {
"type": [
"object",
"null"
],
"properties": {
"city": {
"type": [
"string",
"null"
]
},
"country": {
"type": [
"string",
"null"
]
},
"timezone": {
"type": [
"string",
"null"
]
}
}
}
}
},
"occupations": {
"type": "object",
"properties": {
"category": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"prefLabel": {
"type": "string"
}
}
}
}
},
"preferredFreelancerLocation": {
"type": [
"array",
"null"
]
},
"skills": {
"type": [
"array",
"null"
],
"items": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"prettyName": {
"type": "string"
},
"highlighted": {
"type": "boolean"
}
},
"required": [
"name",
"prettyName",
"highlighted"
]
}
},
"publishedDateTime": {
"type": "string"
},
"ciphertext": {
"type": "string"
},
"recordNumber": {
"type": "string"
},
"amount": {
"type": "object",
"properties": {
"currency": {
"type": [
"string",
"null"
]
},
"rawValue": {
"type": [
"string",
"null"
]
}
}
},
"hourlyBudgetType": {
"type": [
"string",
"null"
]
},
"hourlyBudgetMin": {
"type": [
"object",
"null"
]
},
"hourlyBudgetMax": {
"type": [
"object",
"null"
]
},
"totalApplicants ": {
"type": [
"integer",
"null"
]
}
}
}
},
"required": [
"node"
]
}
},
"pageInfo": {
"type": "object",
"properties": {
"endCursor": {
"type": "string"
},
"hasNextPage": {
"type": "boolean"
}
}
}
}
}
}
}
}
}I split the item jsons (labelled "edges" in the response) into an array for further processing, which I kept within a working JSON for the whole routine I called LoopJSON. I did this in a function:
setProperty(setProperty(variables('LoopJSON'),'edgeArray',body('Parse_JSON')?['data']?['marketplaceJobPostingsSearch']?['edges']),'count',length(body('Parse_JSON')?['data']?['marketplaceJobPostingsSearch']?['edges']))The schema from each item can then be JSON translated again within the Item loop:
{
"type": "object",
"properties": {
"id": {
"type": "string"
},
"title": {
"type": "string"
},
"description": {
"type": "string"
},
"experienceLevel": {
"type": "string"
},
"client": {
"type": "object",
"properties": {
"location": {
"type": [
"object",
"null"
],
"properties": {
"city": {
"type": [
"string",
"null"
]
},
"country": {
"type": [
"string",
"null"
]
},
"timezone": {
"type": [
"string",
"null"
]
}
}
}
}
},
"occupations": {
"type": "object",
"properties": {
"category": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"prefLabel": {
"type": "string"
}
}
}
}
},
"preferredFreelancerLocation": {
"type": [
"array",
"null"
]
},
"skills": {
"type": "array",
"items": {
"type": [
"object",
"null"
],
"properties": {
"id": {
"type": [
"string",
"null"
]
},
"name": {
"type": [
"string",
"null"
]
},
"prettyName": {
"type": [
"string",
"null"
]
},
"highlighted": {
"type": [
"string",
"null"
]
}
}
}
},
"publishedDateTime": {
"type": "string"
},
"ciphertext": {
"type": "string"
},
"recordNumber": {
"type": "string"
},
"amount": {
"type": [
"object",
"null"
],
"properties": {
"currency": {
"type": [
"string",
"null"
]
},
"rawValue": {
"type": [
"string",
"null"
]
}
}
},
"hourlyBudgetType": {
"type": [
"string",
"null"
]
},
"hourlyBudgetMin": {
"type": [
"object",
"null"
],
"properties": {
"currency": {
"type": [
"string",
"null"
]
},
"rawValue": {
"type": [
"string",
"null"
]
}
}
},
"hourlyBudgetMax": {
"type": [
"object",
"null"
],
"properties": {
"currency": {
"type": [
"string",
"null"
]
},
"rawValue": {
"type": [
"string",
"null"
]
}
}
},
"totalApplicants": {
"type": [
"number",
"null"
]
}
}
}For the skill listings that are returned, I split them out in a loop and process them to eliminate jobs that I don't want by skill listing (assuming of course that the job poster knew what they were doing), then combined them in a string for saving in SharePoint along with the job. I also split the Preferred Location countries for looping and filtering, and then recombine them into a single string. I also include thresholds for "Budget" which is the by contract budget limit, and the Salary maximum, still passing through jobs that have zero for both (no limit).
Results
The results in SharePoint look like this:
I also have a Microsoft Access front end, which I plan to turn into a Power App:
Good luck!
Brian Jones
The Blue Wizard
Comments