How to generate an API Key for the Google Spreadsheet Widget

  • 3
  • Article
  • Updated 2 years ago
  • (Edited)
The default Refresh Interval of the Google Spreadsheet Widget is 60 minutes. To use a different Refresh Interval you’ll need to provide your own API Key. Here is how you can do that.

Step 1:

Generating an API Key requires that you create a Project on the Google Developer Console. If you don't have one, you can use this wizard to both create a new Project, and generate an API Key.

First thing you need to do is agree to the Terms of Service. Select Yes, and then Agree and continue.

                     

Now that your project is created and the API is enabled, click Go to credentials.

                    


Step 2:
In the My Project Window, click on Credentials in the side menu, and then from the Create Credentials dropdown, select API Key.

                    


Step 3:
That's all you need! Now you have your API Key!

                    



Copy and paste the API Key into the API Key field on the Spreadsheet Widget Settings.

                    

Once entered into the Widget Settings, you’ll be able to use a refresh rate other than 60 minutes.

---------------
Please note that the Sheets API is limited to 40,000 request per day. The Widget will make one request per the Refresh Interval you provide. To avoid exceeding the quota use the formula below and ensure the result is equal to or less than 40,000.

(Number of Displays showing the Presentation) x (How many instances of the Widget are in the Presentation) x (1,440 minutes per day/Widget Refresh Interval) = Request per day
Example Scenario 1:

1 Display showing the Presentation, and the Presentation contains 1 instance of the Widget, configured with a Refresh Interval of 1 minute will result in 1,440 requests per day.

(1 Display) x (1 Instance of Widget) x (1,440 minutes per day / 1 minute refresh) = 1,440 Request per day
Example Scenario 2:

3 Displays showing the Presentation, and the Presentation contains 2 instances of the Widget, configured with a Refresh Interval of 2 minute will result in 4,320 requests per day.

(3 Displays) x (2 Instances of Widget) x (1,440 minutes per day / 2 minute refresh) = 4,320 Request per day
Photo of Alan Clayton

Alan Clayton, Official Rep

  • 8,788 Points 5k badge 2x thumb

Posted 2 years ago

  • 3
Photo of Darius - PRODO.us

Darius - PRODO.us, Champion

  • 26,850 Points 20k badge 2x thumb
Question. How exactly do I restrict/bind API key to a particular presentation or sheet?
Photo of Donna Peplinskie

Donna Peplinskie

  • 7,142 Points 5k badge 2x thumb
Google Developers Console enables you to restrict requests to particular HTTP referrers. You could try playing around with that to see if you can get such a granular level of control.
Photo of Derek Smith

Derek Smith

  • 730 Points 500 badge 2x thumb
The steps outlined in this article diverge substantially after Step 4 because I'm forced to create a OAuth 2.0 Client ID instead of creating an API Key. After that, none of your examples make any sense.

I work at a school fully tied to Google Apps for Education, and to be honest, I have no experience doing this API stuff.

Why is it so complicated to change the refresh rate? The old Sheets widget or gadget would refresh whenever the sheet itself was altered. This process is so awkward!

Any help would be appreciated. 
(Edited)
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Derek,

I was going to suggest the exact same as Darius. I think a separate Google account would be your best bet here.

Let us know how you make out!
Photo of Derek Smith

Derek Smith

  • 730 Points 500 badge 2x thumb
I can't create a Google account in the Google Apps for Education schema. It also needs to be shared with other members of my school.
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Derek,

Is it possible that creating API keys is restricted in your domain?
Photo of Derek Smith

Derek Smith

  • 730 Points 500 badge 2x thumb
Quite possibly, which makes Rise's API requirement either too difficult or impossible to accomplish.

Can you please just bring back the ability to set a duration in the widget itself? :-)
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Derek,

My understand is that the default Refresh Interval being 60 minutes is a restriction from Google when using Google Sheets. To use a different Refresh Interval,  you need to provide your own API Key.
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Derek,

I've brought this up to our Dev team to see if they can give you some guidance after step 4, and to confirm that our steps are accurate.

The only reason you would need to create a faster refresh rate is if the rate that is included in the Widget itself is not sufficient. If that refresh rate is sufficient, you don't need to add the Spreadsheet API Key.
Photo of Derek Smith

Derek Smith

  • 730 Points 500 badge 2x thumb
I had my Google Admin create the API key for me and now it works.

If I give this API key to other users for the Google Spreadsheet widgets they will use in their own presentations, will it work, or will they need their own API key?
Photo of Darius - PRODO.us

Darius - PRODO.us, Champion

  • 26,850 Points 20k badge 2x thumb
Derek, it will work, but keep in mind that Google allow just that many requests sent by one API for free. I recommend creating new API for each project and be mindful about the refresh times.
(Edited)
Photo of Derek Smith

Derek Smith

  • 730 Points 500 badge 2x thumb
Thanks for the heads up.

We have two campuses with a Lunch menu that displays on three monitors for about 1.5 hours a day. We also display the Athletics schedule on two monitors for about 3 hours a day.

I'm not worried about bumping up against the 40k API requests per day limit. :-)
Photo of Darius - PRODO.us

Darius - PRODO.us, Champion

  • 26,850 Points 20k badge 2x thumb
40K ... I did not know the limit was that high ))). Anyway, I create different API keys for different project just to keep things organized.
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Glad to hear you got it created Derek!
Photo of Donna Peplinskie

Donna Peplinskie

  • 7,142 Points 5k badge 2x thumb
There seems to be a bit of confusion as to why a Google API key is needed to achieve a more frequent refresh interval than 60 minutes.

In a nutshell, the reason for that is because, with the introduction of Google's Sheets API version 4 (which is what we use to get data out of Google Spreadsheets), they introduced a new API key requirement. Prior to version 4, there was no such requirement.

And because each API key has a maximum limit on the amount of requests that can be made each day, we knew we couldn't use the same API key for all users, especially for those users who require a frequent refresh interval. We would quickly reach the rate limits that Google had set if we did that.

Ultimately, that's how we arrived at the decision to set a minimum default refresh interval of 60 minutes, and to require the use of a custom API key for those users who needed a more frequent interval.

Hope that helps to clear things up!
Photo of Lennart Bentsen

Lennart Bentsen

  • 112 Points 100 badge 2x thumb
I'm having trouble getting the API to pull my Google Sheets data.  Does anyone know if I need to configure the API key to work with any specific domains?  When data is pulled from Google Sheets, is that pulled from my media player's IP address/location, or is it pulled by Risevision servers and then pushed to my media player?

Maybe there's something else I'm missing in the API credentials setup?
Photo of Darius - PRODO.us

Darius - PRODO.us, Champion

  • 26,850 Points 20k badge 2x thumb
Have you looked at this: http://prodo.us/dsigns/APIkey.pdf
Photo of Lennart Bentsen

Lennart Bentsen

  • 112 Points 100 badge 2x thumb
That did the trick.  Thank you Darius!
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Thanks for that Darius!
Photo of Jeremy Klammer

Jeremy Klammer

  • 688 Points 500 badge 2x thumb
Question on timing/refresh rate - Is the minimum limit set to refresh rate of refresh: 1 (min) -- meaning a refresh of once every 60 seconds? I tried typing 0.5 but that halts the API from refreshing at all.

I was hoping to get something to refresh on a ~20 sec interval.


Thanks!
Jeremy
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Jeremy, 

It is not limited to every 1 minute, but to 40,000 request per day. 

Take a look at the examples in the original post for some examples to determine what is best for you.  

Thanks!
Photo of Jeremy Klammer

Jeremy Klammer

  • 688 Points 500 badge 2x thumb
Thanks Robb,

I don't see an example that shows how to change the refresh rate and faster than 1 (minute). If I set the refresh rate to 0.5 (for 30 seconds), the data for that widget stops updating.

Do you know how to alter the refresh rate to be in terms of ~30 seconds instead of whole integer minutes?


Cheers
Jeremy
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Jeremy,

I don't, sorry...I wonder if 1 minute is the minimum. I thought that you would be able to set it to less than that, as long as it did not exceed the 40,000 requests per day, but that does not appear to be the case with what you are seeing.
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Jeremy,

I was just talking to one of our Dev's about this, and the code doesn't restrict any value, but you won't be able to enter anything less than a minute in the settings, because that field is interpreting the value in minutes. So the short answer is the lowest value you can select is 1, for 1 minute.

If you can work with our rise-google-sheet component  (https://github.com/Rise-Vision/rise-google-sheet) directly, then there is no restriction.

Thanks!