Google Sheet function pull access_token in API

[hannd2412]

Hello guys,

I have a job that has to do with Google Sheets and would love to get your support in the MrExcel community

I have a sheet like this.

Phone	API URL	Access_Token_from_API_URL
09199999999	https://icc-api.stringee.com/v1/but...UXE3L1pZUnplcWt0T09zMw==&username=09199999999
08188888888	https://icc-api.stringee.com/v1/but...UXE3L1pZUnplcWt0T09zMw==&username=08188888888

I would like to ask you to help me create a Google Sheet function located in the column Access_Token_from_API_URL to get the value of Access_Token in the link located in the column API_URL.
It is the cluster of characters that are bolded below
{"r":0,"msg":"Success","data":{"access_token":"eyJjdHkiOiJzdHJpbmdlZS1hcGk7dj0xIiwidHlwIjoiSldUIiwiYWxnIjoiSFMyNTYifQ.eyJqdGkiOiJTS0dvSkxlWkZuRHhYZjRtd0U3cUllYjJTa3dFRE5ZYy0xNjM5NjU5MzA3IiwiaXNzIjoiU0tHb0pMZVpGbkR4WGY0bXdFN3FJZWIyU2t3RUROWWMiLCJleHAiOjE2Mzk3NDU3MDcsInVzZXJJZCI6IjA5MTk5OTk5OTk5IiwiY2FuQ2FsbG91dCI6ZmFsc2V9.d6CnwcmHDI1MOOaBsrTHm6P1-YZUizY_PLh0kyNRSL4"}}

Thank you for reading and helping me. Wishing you all a merry and peaceful Christmas!

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I really need a Google Sheet function pull access_token in API
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[hannd2412]

I am also asking for a similar support at excel forum https://www.excelforum.com/for-other-platforms-mac-google-docs-mobile-os-etc/1366330-i-really-need-a -google-sheet-function-pull-access_token-in-api.html#post5607998

 

[veyselemre]

Hi, try this.

=index(split(index(split(index(split(importxml("https://icc-api.stringee.com/v1/buttoncall/accesstoken?key=UmZzaXN1d2tXejVDYTVxWGQ1M2pZUE9FcWtsWGtRdy9JWFRPTS9lZXhtVG9CaDY5UXE3L1pZUnplcWt0T09zMw==&username=09199999999","//*"),"|"),2,1),":"),1,5),""""),1,1)

 

[hannd2412]

Hi, try this.

=index(split(index(split(index(split(importxml("https://icc-api.stringee.com/v1/buttoncall/accesstoken?key=UmZzaXN1d2tXejVDYTVxWGQ1M2pZUE9FcWtsWGtRdy9JWFRPTS9lZXhtVG9CaDY5UXE3L1pZUnplcWt0T09zMw==&username=09199999999","//*"),"|"),2,1),":"),1,5),""""),1,1)

Thank you for responding.
I applied your sample code to 500 links, the cell only returns Loading