How to use REST API (Zoho Sheets) in Excel VBA?

Question

I require help creating a VBA program to access an application called ZohoSheets API. I'm getting an error: "Invalid data for found for parameter [method]" (it may look like a typing mistake, but this is the response I've got).

I've tried various example code I've found on this website. I haven't been able to get any of it to work. Example (I've placed the xxx's as placeholders. They weren't used during runtime.):

Option Explicit

Sub ZohoSheets()

Dim XMLHTTP
Dim URL As String
Dim parameters As String 

Set XMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
parameters = "method=worksheet.list"
URL = "https://sheet.zoho.com/api/v2/xxxx"

XMLHTTP.Open "POST", URL, False
XMLHTTP.setRequestHeader "Authorization", "Zoho-oauthtoken 1000.xxx.xxx"
XMLHTTP.send parameters

MsgBox XMLHTTP.responsetext

End Sub

This is what I received: {"error_message":"Invalid data for found for the parameter [method]", "error_code:":2878}

Please help. Thanks!

Can you supply a link to the documention which specifies doing the POST as shown above? — QHarr
– QHarr, Commented Jul 16, 2019 at 18:41
What API endpoint are you trying to hit? You've obscured that part of the URL and your procedure name says nothing about what you're trying to do. That said none of the documented endpoints appear to have a method parameter. (CC @QHarr) — Mathieu Guindon
– Mathieu Guindon, Commented Jul 16, 2019 at 18:52
@MathieuGuindon I eventually found it sheet.zoho.com/help/api/v2/#READ-List-all-worksheets — QHarr
– QHarr, Commented Jul 16, 2019 at 18:54
@MathieuGuindon Lol. I used the site's test facility and monitored via fiddler. It indeed makes a POST. Buried away is the phrase Post Parameters: method=worksheet.list There is no mention of the additional header that I saw. — QHarr
– QHarr, Commented Jul 16, 2019 at 18:56
Nice detective work, upvoted! ...still, HTTP POST to get a list of things seems completely backwards. Glad I'm not putting up with that API! — Mathieu Guindon
– Mathieu Guindon, Commented Jul 16, 2019 at 18:59

QHarr · Accepted Answer · 2019-07-16 20:12:04Z

1

You need an additional header. I used the site's test facility and monitored the web traffic with dev tools and fiddler. Tried out the header I thought most logical from those included. Note tokens are time limited.

XMLHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded;charset=UTF-8"

VBA:

Option Explicit
Public Sub GetListOfSheets()
    Dim url As String, parameters As String

    With CreateObject("MSXML2.XMLHTTP.6.0")
        parameters = "method=worksheet.list"
        url = "https://sheet.zoho.com/api/v2/resource_id"
        .Open "POST", url, False
        .setRequestHeader "Authorization", "Zoho-oauthtoken 1000.xxx.xxx"
        .setRequestHeader "Content-type", "application/x-www-form-urlencoded;charset=UTF-8"
        .send parameters
        MsgBox .Status
    End With

End Sub

edited Jul 16, 2019 at 20:12

answered Jul 16, 2019 at 18:54

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Non_binary_robot Over a year ago

Thanks for your response and help! I've typed that in, but it didn't work. I'm getting the same error. Is there a different way to add the parameters?

QHarr Over a year ago

No. I tested with vba as well and it works. I will add full script with personal details removed. Tokens are time limited so have you completed within time limit ?

QHarr Over a year ago

I assume you have already created a workbook and have the resource id? As well as fresh token?

Non_binary_robot Over a year ago

Nevermind! It worked! I used the code you provided! Much appreciate your help!! This thing was driving me crazy.

Mathieu Guindon Over a year ago

@Non_binary_robot please consider marking this answer as accepted, by ticking the hollow checkmark next to the up/down voting buttons near the top of this post.

|

Collectives™ on Stack Overflow

How to use REST API (Zoho Sheets) in Excel VBA?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related