{"info":{"_postman_id":"5dc51f3f-c7b0-4c66-a457-11af32bb1a63","name":"Data Driven using googlesheet","description":"

$\"\"$

Description

This collection shows how to execute data driven execution without using data file. Here we use Google sheet to drive our execution. we are accessing Google sheet through Google sheet API

Usage instruction

Google sheet Authorization:

Step 1.
Create google sheet at : https://docs.google.com/spreadsheets/u/0/

Step 2.
Copy the sheet id (shown as highlighted)
https://docs.google.com/spreadsheets/d/1OllrvY6KCOR1GbByUBNCDPR6ikRzuWcODN6fRLgaWFw/edit#gid=1386834576

Step 3

Goto :

https://developers.google.com/sheets/api/quickstart/python

Click: Enable sheet API

or directly login to you google console and search for sheet api and enable it :

https://console.cloud.google.com/apis/library?project=arched-cabinet-307912

Select : Webbrowser as the app type and set : https://www.example.com as the javascript origin and Authorized redirect URI

Click Download client Configuration

Step 4

Goto: \"Retrieve Google sheet\" request's Authorization tab in postman and replace the client id and client secrete ,
Click: Get new token and then use new token. ( allow any authorization by clicking continue any way)
Now you are authorized to Do get request

Now you can Execute the collection

set google sheet content as :

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n

username	password	expectedusername
user1	pass1	user1
user2	pass2	user2
user3	pass3	user3

Execute the collection using newman or collection runner

\n","schema":"https://schema.getpostman.com/json/collection/v2.0.0/collection.json","toc":[{"content":"Description","slug":"description"},{"content":"Usage instruction","slug":"usage-instruction"}],"owner":"8296678","collectionId":"5dc51f3f-c7b0-4c66-a457-11af32bb1a63","publishedId":"TzXtGzS2","public":true,"customColor":{"top-bar":"FFFFFF","right-sidebar":"303030","highlight":"EF5B25"},"publishDate":"2021-05-21T09:55:00.000Z"},"item":[{"name":"Retrieve Google sheet","event":[{"listen":"test","script":{"id":"32206c8b-1a34-458c-8a8a-8d1a74154492","exec":["pm.variables.set(\"records\",pm.response.json().values)\r","\r",""],"type":"text/javascript"}},{"listen":"prerequest","script":{"id":"ded967e5-d09e-4fda-876f-dbaa8a1f6dda","exec":[""],"type":"text/javascript"}}],"id":"6dda15f2-306b-4915-a585-e4669c854654","protocolProfileBehavior":{"disableBodyPruning":true},"request":{"auth":{"type":"oauth2","oauth2":{"basicConfig":[{"key":"accessToken","value":""}],"advancedConfig":[{"key":"clientSecret","value":""},{"key":"clientId","value":""},{"key":"tokenType","value":""},{"key":"redirect_uri","value":""},{"key":"useBrowser","value":""},{"key":"accessTokenUrl","value":""},{"key":"authUrl","value":""},{"key":"client_authentication","value":""},{"key":"scope","value":""},{"key":"tokenName","value":""}]},"isInherited":false},"method":"GET","header":[],"body":{"mode":"raw","raw":"","options":{"raw":{"language":"json"}}},"url":"https://sheets.googleapis.com/v4/spreadsheets/1U97JC0X8F54p9xZ6kMeHSJ4qldyJokf4FZgsJKxHH2E/values/A1:Z10000001","description":"

Description

This collection shows how to execute data driven execution without using data file. Here we use Google sheet to drive our execution. we are accessing Google sheet through Google sheet API

Usage instruction

\n","urlObject":{"protocol":"https","path":["v4","spreadsheets","1U97JC0X8F54p9xZ6kMeHSJ4qldyJokf4FZgsJKxHH2E","values","A1:Z10000001"],"host":["sheets","googleapis","com"],"query":[],"variable":[]}},"response":[],"_postman_id":"6dda15f2-306b-4915-a585-e4669c854654"},{"name":"Data driven","event":[{"listen":"test","script":{"id":"800bd261-a41c-4798-995e-8966f2e8984d","exec":["pm.test(\"Your test name\", function () {\r"," var jsonData = pm.response.json();\r"," pm.expect(jsonData.args.username).to.eql(pm.variables.get(\"expectedusername\"));\r","});"],"type":"text/javascript"}},{"listen":"prerequest","script":{"id":"b957b2d3-968b-4148-a180-f3051951e39c","exec":["//pm.variables create local variable\r","//life time of local variable is collection run\r","//hence for each collection run start index will return undefined. This indicates that collection just started\r","if (!pm.variables.get(\"index\")) {\r"," pm.variables.set(\"index\", 1)\r","}\r","\r","records = pm.variables.get(\"records\")\r","index = pm.variables.get(\"index\")\r","\r","//records[0] contains column names (which equates to variable names)\r","\r","if (index !== records.length) {\r","\r"," //iterate and set the variable name and value as local variables\r"," for (let i in records[0]) {\r"," pm.variables.set(records[0][i], records[index][i])\r"," }\r"," pm.variables.set(\"index\", ++index)\r"," // you can set it postman.setNextRequest(null) if you don't want to execute any other reqeust.\r"," pm.variables.get(\"index\")===records.length?null:postman.setNextRequest(pm.info.requestName)\r","}\r",""],"type":"text/javascript"}}],"id":"86c9451a-43fa-4e54-9adc-55d3e4885fce","protocolProfileBehavior":{"disableBodyPruning":true},"request":{"method":"GET","header":[],"body":{"mode":"raw","raw":"{\r\n\"username\": \"{{username}}\",\r\n\"password\": \"{{password}}\"\r\n}","options":{"raw":{"language":"json"}}},"url":"postman-echo.com/get","urlObject":{"path":["get"],"host":["postman-echo","com"],"query":[],"variable":[]}},"response":[],"_postman_id":"86c9451a-43fa-4e54-9adc-55d3e4885fce"}],"variable":[{"key":"variable1","value":""}]}