startWebRequest() for Google Sheets

CS Principles Coding and Debugging Help
1

I have had a couple of questions come up from students who would like to use a Google Sheet to store data for access in AppLab, either because they are familiar with the tools in Google Sheets, or because they want to use a connected Google Form. It seems like the startWebRequest is the optimal tool for doing this, since then the data could be dynamically linked to their app, rather than needing to import static data from Google Sheets when it was ready.

Looking at the documentation for startWebRequest(), I can see that sheets.google.com is among the supported urls, but so far I have been unable to use a Google Sheet with a startWebRequest() call.

Can someone walk me through any steps necessary to set up a Google Sheet for sharing with an AppLab app - Make it public? Publish to the web? And then show me how to format the Google Sheets URL so that I can use it with a startWebRequest() call in AppLab. Thanks for your help!

2

@jswilliams I will pass this on to the team for proper documentation. The startWebRequest() requires spreadsheets.google.com, however, that access url is no longer used by google (docs.google.com/spreadsheets)

3

Thanks @terence.stone25 that certainly helps to explain why I was having such trouble getting them to work!

4

I believe that you would need the Google Sheets API in order to get any sheets that you may have. This would require generating an API key at Google’s developer page, which in my opinion is a lot of work, and much better alternatives exist.

If you do however set up this API key for your project, you can use the library Ocelot that can get the source of any website for Applab.
Import the library id _8WicO3r9MEzT1RydvV-nwmMHGIOqz58dP2EBCdyZQo, then run the code in your project:

Ocelot.get('https://example.com',function(data){
  console.log(data);
});

Replace the example domain with the url of your API key for your sheets page and there you go, should work.

1 Like
5

Thanks @letti42 I’m with you that does look like more work than it’s worth - and likely a little more than my students are up for. You mentioned better alternatives exist. I’d love to hear what you’re thinking?

6

Yes, I mentioned alternatives though I should’ve specified what I meant a bit further - after some research, I discovered the website Sheety. You can link your sheets documents there and it creates a url with all the responses as an API for free. It’s quick and easy (took me less than a minute), and paired with the before mentioned Ocelot library it may be your best solution.

1 Like
7

Hi
Any updates to a URL being accepted as https://docs.google.com/spreadsheets ?

8

Hi,
I’ve tried importing the library by using the ID you have given but I only get one function showing ‘OcelotMin.Load()’

Can you help ?