Appending to Google Spreadsheet API

Hi, How am I able to POST this value to my API?
{  
   "values":[  
      [  
         "11:00 AM",
         "Name Last",
         "Globe",
         "Company",
         "No Reason",
         "2000",
         "c:/"
      ]
   ]
}


I recieve 400 error everytime

Hi Reyco Seguma,

Did you checked how and why the API is providing you a bad Request (might be their format)? 


Marco Arede wrote:

Hi Reyco Seguma,

Did you checked how and why the API is providing you a bad Request (might be their format)? 


Hi.
Google Spreadsheet API is using this JSON format to send POST request.

I tried using text/plain but still it doesn't work.
It just returns 400.
I already check the JSON format on JSON formatter it was correct.


I manage to make it work..
but I used Javascript as a requestor.

Hi Reyco Seguma,

Please let us understand more about the context of your problem. 

  1. What code are you using, the forge connector? https://www.outsystems.com/forge/component-overview/3058/google-sheets-connector
  2. In your debugger, what does it say about the executed Error? (maybe it can point towards the right direction)
  3. Did you checked the API documentation? Your json sintax might be correct, but it might expect another type of object, or to be send in body as text or not.
  4. Also, did you made sure that both your api's are enabled? To access drive and spreadhseets?
  5. It could be the case that authentication needs to be provided as well.

Hi Reyco Seguma,

Good to know it's solved. 

Can you tell us more details on how you solved? What's the difference between what you had before and the Javascript you have now?

Cheers!

Reyco Seguma wrote:

Marco Arede wrote:

Hi Reyco Seguma,

Did you checked how and why the API is providing you a bad Request (might be their format)? 


Hi.
Google Spreadsheet API is using this JSON format to send POST request.

I tried using text/plain but still it doesn't work.
It just returns 400.
I already check the JSON format on JSON formatter it was correct.



I enable the Spreadsheet Library on the Google API console.
So that i'll be able to access the Google Spreadsheet API by adding the "scope"
for the spreadsheet.

By adding the scope of the spreadsheet during the login, it returns a token with
permission Spreadsheet API. 

Hi Reyco Seguma,

Thanks for sharing that. I think you can mark this post as solved.

Good luck with the app!

Solution

Marco Arede wrote:

Hi Reyco Seguma,

Good to know it's solved. 

Can you tell us more details on how you solved? What's the difference between what you had before and the Javascript you have now?

Cheers!

I have no problem on getting data from Google API.
But I am having a problem on POST request.

I tried everything to use the OS Rest API to consume an API from Google.
with Oauth2 and spreadsheet scope. But, I am having problem with the JSON structure
given by the Google API.

{  
   "values":[  
      [  
         "11:00 AM",
         "Name Last",
         "Mark",
         "Company",
         "No Reason",
         "2000",
         "c:/"
      ]
   ]
}


I tried to use text/plain for the POST body but still it doesn't work.

So I decided to use Javascript request instead to trigger the post request.
It successfully posted the value.

Here is my solution:

SyntaxEditor Code Snippet

var xhr = new XMLHttpRequest();
var url = 'https://sheets.googleapis.com/v4/spreadsheets/" + SpreadsheetId + "/values/Sheet1:append?valueInputOption=USER_ENTERED&key=" + APIKey + "';

xhr.open('POST', url, true);

xhr.setRequestHeader('Content-Type', 'application/json');
xhr.setRequestHeader('Authorization', 'Bearer xxxxxxxx');

xhr.onreadystatechange = function () {
    if (xhr.readyState === 4 && xhr.status === 200) {
        var json = JSON.parse(xhr.responseText);
    }
};
var data = JSON.stringify({  
   "values":[  
      [  
         "11:00 AM",
         "Name Last",
         "Mark",
         "Company",
         "No Reason",
         "2000",
         "c:/"
      ]
   ]
});
xhr.send(data);


If you have a better solution. I will take a look at it.
thanks.

Solution