Integrating Google Sheets
Last updated
Last updated
Optimism Etherscan's APIs provides a convenient way to connect and import block explorer information for developers to use in your own apps and services .
Having technical knowledge is not a requirement for using APIs however, and non-developers can make use of the available endpoints to build your own dashboards and statistics by importing API data to Google Sheets .
A valid Optimism Etherscan API key and a Google Account is required to follow along this tutorial.
In a new Google Sheets document, head over to Extensions
> Apps Script
.
We'll be utilizing an open source script called ImportJSON developed by @bradjasper and @tommyvernieri to help parse JSON responses returned by the API endpoints.
Paste the source code into the script editor, optionally you can rename the file to ImportJSON.gs
.
Add a new Sheet from the small "+" icon at the bottom left and name it AutoRefresh
.
Back to Google Apps Script, create a new file and name it AutoRefresh.gs
. Paste in this following code.
The idea of this script is to generate a random number to the AutoRefresh sheet, which is then appended to the end of the ImportJSON request.
Google Sheets only performs a new request if it detects that a formula has changed.
Select the function as AutoRefresh
, event source as Time Driven
, time based trigger to Minutes Timer
and minute interval to Every Minute
.
Once you have added both scripts, go back to Sheet1 and start making API endpoint calls with the following syntax in cell A1.
While the above was a straightforward method to call an API endpoint and output the response into a Google Sheet, it could use further optimizing and data formatting.
With the script in place, we can run this function every minute by going to Triggers
> Add Trigger
at the left panel.
Parameter | Description |
---|---|
The query above will return the following response, and refreshes auto-magically every minute!
Checkout some prepared dashboard examples we have, simply duplicate this by going to File > Make a copy
and go about customizing this to your taste!
url
the endpoint url to make requests from, with your API Key
query
comma separated paths to import, such as /result
or /result/SafeGasPrice
parseOptions
list of options to process the returned data, either noInherit
, noTruncate
, rawHeaders
, noHeaders
or allHeaders