Getting started with the XBRL Google Sheet and add-on

[David Tauriello]

Watch an introductory webinar replay about retrieving data with the XBRL API

Please follow the steps below to get data from the XBRL US Database of Public Filings in a Google Sheet.

Installation:

1. Install the Google Sheets XBRL Filed Data Add-on to your Google Account first, using a Chrome browser (NOTE: This add-on will not work with Firefox.). Want to use an existing email Google, without creating a Gmail account? Visit https://accounts.google.com/SignUpWithoutGmail.
    
2. Activate the add-on under the “Extensions” menu on Google Sheets – choose the XBRL Filed Data >> Login / Query / Validate item.

   Click the login button in the panel that opens, and a browser window shows the XBRL US login prompt, where you can either login with your XBRL US Web account, or click ‘Sign in with Google’. Once you’re authenticated, a ‘Success!’
   message appears, and you can close the browser window that opened.

3. After you’ve activated the Add-on, re-open the Add-on by
   selecting XBRL Filed Data >> Login / Query / Validate item from
   the "Extensions" menu again.
4. Choose one of the functions in the task pane from the dropdown, at least one filter criteria, and the fields that should populate from the ‘Data to Return’ section of the Query Wizard task pane. Select a radio button option for what should be added to the spreadsheet (the query and data, only the data or only the query) then click the Get button.
5. Once you’ve confirmed that the =showData custom function is working, open a Google Sheet template and use the make a copy option under the File menu from within your Google account.

   Do not use the ‘request edit access’ option under the view button or ‘Add to My Drive’ under the file menu – these will not work. Using “Make a copy…” under the File menu creates an independent copy of the XBRL Spreadsheet in your Google Account

6. The Validate tab on the task pane lets you check an SEC, FERC or ESEF filing with business rules specific to US GAAP, FERC or IFRS taxonomies.

Troubleshooting:

If you’re having trouble getting data to appear in your copy of the Google Sheet, try the following in the listed order, and stop when your issue is resolved:

1. If you’re using the ‘Sign on with Google’ option to login, make sure your Google Account has a password – this is a requirement.
    
2. Open your copy of one of the Google Spreadsheet templates in the browser’s incognito (Chrome) or private (Edge) mode, then open the XBRL Filed Data Add-on’s task pane, select a function, add criteria for a search and click ‘Get’ to initiate a query. When you return to the ‘regular’ browsing tab, the issue should be resolved (you might need to refresh the browser tab).
    
3. Toggle ‘Use in this document’ and/or uninstall and re-install the XBRL Filed Data Add-on. Choose the “Manage Add-ons” option under the Add-ons menu, click the three dots in the top-right corner of the XBRL API access Add-on. If you have a ‘Use in this document’ option with a checkmark, click to uncheck, then repeat to re-apply the check and return to the sheet to change/test a query.
   If you’re still unable to return data, return to the three dots for the Add-on and uninstall. Reinstall the add-on from https://xbrl.us/xbrl-api-addon, then use the XBRL Filed Data Add-on menu option to display the task pane and log in. Once the Add-on is authorized, select a function, add criteria for a search and click ‘Get’ to initiate a query.
    
4. Use the advanced search and replace to add a * in front of the initial = for all formulas (be sure to check the ‘search in formulas’ option) in the Google Sheet. This will stop all formulas. Then reverse the find and replace to remove the * from the front of the formulas.
    
5. move all copies of Google Sheet templates from XBRL US to the trash and then make a new copy of a Google Sheet template.
    
6. Clear the cache in your browser, then close and re-open your copy of the Google Sheet template.

Tried everything and it still won’t work? Post a reply here.

• This topic was modified 4 years, 3 months ago by David Tauriello.
• This topic was modified 4 years, 3 months ago by David Tauriello.
• This topic was modified 4 years, 3 months ago by David Tauriello.
• This topic was modified 3 years, 4 months ago by David Tauriello.
• This topic was modified 10 months, 2 weeks ago by David Tauriello.
• This topic was modified 10 months, 2 weeks ago by David Tauriello.

[Natasha Pchelko]

Getting error while activating the Add-on:

‘Error: Error retrieving token: unsupported_scope_type, Unsupported Scope Type (line 467, file “Service”, project “OAuth2”)’.

As a result can’t activate the add-on with the XBRL API Access >> Authentication Menu

[David Tauriello]

Hi Natasha – thanks for writing and apologies for the issue you had getting the add-on to authenticate. I’ve reset your record in our system – please try to use the add-on authentication again, and reply back if you’re still having issues.

[Ashutosh Pandey]

Data is not being loaded even after all the troubleshoots. Though CIK gets loaded for selected ticker.

[David Tauriello]

Hi Ashutosh – on the Main Sheet of the XBRL Spreadsheet, what do you see when you change the query dropdown (cell B2) to a different query?

Have you tried removing the add-on from your Google account (Add-ons >> Manage Add-ons) and then re-installing it to your Google account – ? If not, please try that and post a reply if you continue to have an issue (and please share the specific text that’s displayed where data should appear on Main Sheet (cell A11)

[Ashutosh Pandey]

It Displays on A11 cell of main sheet as well as on A24 of BSCompare Sheet “No information returned”.

I have tried removing and reinstalling the add on also but did not work.

As I mentioned in the earlier post; when I change the ticker in the green cell of BSCompare Sheet the CIK also changes but other than that nothing happens.

[David Tauriello]

On the XBRL API Access add-on’s Authentication menu that appears on the side of the Google Sheet, do both the ‘Authorize the API’ and ‘Logout’ links appear to work properly?

After clicking the ‘Authorize the API’ link, have you tried both login options (XBRL US Web Account / Continue with Google) with the same result?

If you’re able to use Chrome web browser, do you get the same result (no information returned)?

If you have downloaded multiple copies of the XBRL API Spreadsheet, please delete all but one of them, or at least move them to a folder in your Google Drive account.

The ticker uses a different call, so it will function regardless of the status of the XBRL API Access add-on.

[Ashutosh Pandey]

I am able to fetch the data by using google authentication. Thank you.

[David Tauriello]

Getting ‘No Information Returned’ – ? Try this:

1. Make (or open) a copy of this file in your Google account: https://xbrl.us/xbrl-api-spreadsheet
2. Open the XBRL API add-on and click the authentication link so that your account is logged out
3. Close and re-open Chrome browser (Chrome will work best here)
4. Open the browser’s settings and clear the cache (maybe just the last 24 hours)
5. Open your copy of the file in the link above
6. Open the XBRL API add-on and click the link to authenticate – confirm you are logged out, first
7. Select either authentication option (XBRL US web account or Continue with Google)
8. Change the Main Sheet Query dropdown (cell B1) to any other query – data should now be flowing through the API via your account again.

Tried everything in this thread and it’s still not working? Post back and let us know you’re still not able to use the API with your account.

[Kevin Donaghy]

Hi

At the following step:

activate the add-on with the XBRL API Access >> Authentication Menu
Click the login link in the panel that opens, and a browser window shows the XBRL US login prompt, where you can either login with your XBRL US Web account, or click ‘continue with Google’. Once you’re authenticated, a ‘Success! You can close this window.’ appears, and you can close the browser window that opened.

I receive the following error:

Sign in with Google temporarily disabled for this app
This app has not been verified yet by Google in order to use Google Sign In.

I am using my Google account and Google password. I have also tried my XBRL.US password (username is identical to Google username).

Any suggestions?
Thanks
Kevin

[Kevin Donaghy]

It appears that logging into to the XBRL.US website via my Google account resolved the issue. Thanks anyway

[David Tauriello]

Kevin – thanks for confirming you’re all set. This was actually caused by Google’s team, looking for additional details from us about the add-on.

[James McKinney]

I could not get to work on Firefox – At the Authentication Menu, I was never given a menu to login – including following all of the steps anove. It did work on Chrome.

[David Tauriello]

Hi James – I’m confirming there’s an issue with the link to the Authentication Menu with Firefox. As a work-around, right-click (Windows) and open the Authentication Menu in a new tab or window, then proceed with credentials and close the tab upon ‘success’ messaging.

[Elmhurst]

Getting an invalid request re-inserting URLs to Config sheet B2 and B3 “expired token.”

[David Tauriello]

Elmhurst – thanks for writing; open a ‘private’ or ‘incognito’ window and login to your copy of the XBRL API Spreadsheet. You’ll also need to login to the XBRL API add on under the tools menu.

Change at least one of the tickers from the default – this will trigger queries.

[Elmhurst]

It worked. Thanks!

[Elmhurst]

Here is the correct image:

[James McKinney]

Getting error in ALL and on the BSCompare tab. Sheet worked two days ago when testing. Repeated steps on a new machine, aPI install, and new copy of sheets and receive same message. Error
Request failed for https://api.xbrl.us returned code 401. Truncated server response: {“error”:”invalid_request”,”error_description”:”Bad or expired token”} (use muteHttpExceptions option to examine full response) (line 106).
Is there a problem with the server? A few students who attempted had the same problem.

[David Tauriello]

Hi James – it sounds like you may have an expired token in your browser that’s not being properly cleared and refreshed. Please open the URL to your copy of the XBRL API Spreadsheet in the browser’s incognito (Chrome) or private (Edge) mode. When you return to the ‘regular’ tab, the issue should be cleared – you might need to refresh the browser tab.

Let me know if this does not resolve the issue, and thanks for your institution’s interest in XBRL – send a note to info@xbrl.us if there’s anything we can do to help your work on this topic.

[Chinmay Laddha]

Hello, Getting an error while changing the options in the drop-down menu:

Request failed for https://api.xbrl.us returned code 401. Truncated server response: {“error”:”invalid_request”,”error_description”:”Bad or expired token”} (use muteHttpExceptions option to examine full response) (line 106)

How to remove this error given my API is already authenticated

[David Tauriello]

Chinmay – you might need to clear the web browser’s cache to resolve this issue, as it sounds like there may be some older token detail that needs to be refreshed.

[morjanah]

I could not get to work on Firefox

[David Tauriello]

Morjanah – please review the instructions at the top of this post, and try installing the add-on to your Google Account using the Chrome web browser first, before moving to Firefox.

[J C]

Note that this is the same for Safari. If you open it in a new tab, you can authenticate and refreshing the sheet should populate the data.

[Robert Koppensteiner]

Is this XBRL Google Sheet and add-on free of charge ? or do I need to pay a membership fee ?

[David Tauriello]

Hi Robert – thanks for writing. Anyone can use the XBRL API and data from our Public Filings Database. XBRL US Members will return more rows with a query (if the additional rows exist), and all XBRL US Members except our Basic Individual Membership are not capped by the number of queries that can be made.

If you’re considering publishing an article or research based on conclusions drawn from use of our Database, or if you’re interested in commercial access, you’ll need an Organizational Membership.

See this page for complete details.

[hypnos hyp]

Data is not being loaded even after all the troubleshoots. Though CILK gets loaded for selected ticker.

[David Tauriello]

Hi Hypnos – does the Google Sheet side panel appear when you use the menu options: Add-ons >> XBRL API Access >> Authentication Menu – ?

If the Authentication Menu shows you as logged in (logout button appearing in the panel), what happens if you create a new Google Sheet (not a copy of one of our templates), click your cursor into cell A1 and paste the following into the formula bar above the spreadsheet (you might need to remove line breaks):

=showData("https://api.xbrl.us/api/v1/fact/search?entity.cik=0001108524&concept.local-name=RevenueFromContractWithCustomerExcludingAssessedTax&period.fiscal-period=Y&period.fiscal-year=2020&fact.ultimus=true&fields=entity.name,period.fiscal-year.sort(DESC),fact.numerical-value,member.local-name.sort(ASC),dimensions")

If that doesn’t resolve the issue, what happens if you move to a browser on another computer (on a different network, if you’re in an organization or within a VPN) and log in?

[Chris Martinez]

Hi David
Unable to get any Query to work (other than the first one), from the several options available on the Main Sheet (cell B2).
Have carried out the following steps, below, but still not effective:
-erased all old copies of XBRL API sheets
– created a folder for all new copies of API sheets
– cleared cache
-ensured that I am “authorized” to request data
– error message received “unknown function – show data”

Unsure how to correct this situation – any suggestions appreciated.
Chris

[David Tauriello]

Hi Chris – see https://xbrl.us/forums/topic/api-google-sheet/#troubleshooting – step 3 or 4 should clear your issue (there are two techniques to try in 4 – removing the ‘root’ for the query, and disabling all functions in the sheet for a moment, then re-enabling them.

[Chris Martinez]

Hi David
I carried out the suggestions (steps 3 and 4) in your recent response (#194230). Unfortunately it did not overcome the issue – I still received the same error message.
Any further suggestions?
Thanks,
Chris

[David Tauriello]

Hi Chris – I just sent an invite for google meet; if you don’t see this until after the time expires, please email me a couple of ranges that work.

Thanks,
David

[David Tauriello]

Hi Chris – thanks for meeting today to resolve the issue you had getting connected with Google Sheets. I’m hopeful you won’t have issues going forward.

For what it’s worth, we do have an Excel Add-in as well, since you mentioned being more comfortable with that application (requires Office 365). See https://xbrl.us/xbrl-api-community for details.

[Chris Martinez]

Hi David,
I used the “XBRL API Classroom Starter”worksheet as my base. I “ran” the Facts for 2018 from Apple 10-K query and it worked.

I then amended one command from the above query; changed 2018 to 2019. I copied the amended commands and placed them on a new Google Sheet. Also ensured that I was authorized to access the API.

The new Google sheet did not function.

The commands that I used are set out below.

Any suggestions?

Thanks,
Chris

https://api.xbrl.us/api/v1/fact/search?dts.id=306447&period.fiscal-year=2019&fields=concept.local-name.sort(ASC),fact.decimals,fact.value,unit,entity.name,entity.cik,repot.filing-date,report.id,entity.id,fact.ultimus-index,fact.id,dimensions,dimensions.count,period.fiscal-year.sort(DESC),period.fiscal-period.sort(DESC)

[David Tauriello]

Hi Chris – thanks for writing; the query needs to be wrapped quotes and be in the first postition in the =showData() function in order to work.

paste this over the top of the cell where you put the query:

=showData(“https://api.xbrl.us/api/v1/fact/search?dts.id=306447&period.fiscal-year=2019&fields=concept.local-name.sort(ASC),fact.decimals,fact.value,unit,entity.name,entity.cik,repot.filing-date,report.id,entity.id,fact.ultimus-index,fact.id,dimensions,dimensions.count,period.fiscal-year.sort(DESC),period.fiscal-period.sort(DESC)“)

You should get two rows – a header and the data for 2019:

concept.local-name fact.decimals fact.value unit entity.name entity.cik report.id entity.id fact.ultimus-index fact.id dimensions dimensions.count period.fiscal-year period.fiscal-period
EntityCommonStockSharesOutstanding -3 4745398000 shares APPLE INC 0000320193 226128 5927 1 188336575 null 0 2019 2018-10-27

[Chris Martinez]

Thanks, David

[Chris Martinez]

Hi David,
I amended the Sample Google sheet that displayed Total Assets for Apple over a number of years. Specifically, I removed Total Assets and introduced Current Assets – it worked.
Then I introduced both Total Assets and Current Assets – but I was not successful. The formula is set out below.

https://api.xbrl.us/api/v1/fact/search?entity.cik=0000320193&concept.local-name=assetsTotal,assetsCurrent&fact.has-dimensions=false&fact.ultimus=true&period.fiscal-period=Y&fields=entity.name,fact.value,fact.decimal,unit,report.filing-date.sort(DESC),concept.local-name,period.fiscal-year,dts.id

Any comments would be appreciated.

Thanks,
Chris

[David Tauriello]

Hi Chris – thanks for writing; assetsTotal is not a US GAAP taxonomy concept and is not an Apple extension.

If you open the latest Simple Statement and Disclosure template posted on the XBRL Data Community this month, and load up an Apple report, you can see the element in column N (taxonomy concept). Apple – like most companies filing with the US GAAP taxonomy – uses the US GAAP concept Assets. to report this number on its balance sheet.

Also, on the fact wizard in the XBRL Filed Data extension you can browse elements. If you enter ‘Assets’ and scroll the list, You’ll find the corresponding element; Apple has chosen to create a preferred label called “Total assets”.

[Chris Martinez]

Thanks David

[Author]

Posts