Home Forums The XBRL API Getting started with the XBRL Google Sheet and add-on

Viewing 13 reply threads
  • Author
    Posts
    • #111838
      David Tauriello
      Keymaster
      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 XBRL API Access Add-on to your Google Account first, using Chrome browser. Want to use your existing email with these resources, without creating a Gmail account? Visit https://accounts.google.com/SignUpWithoutGmail.

        We’ve had some users report issues when they have installed the add-on with Firefox and Edge.

      2. 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

      3. 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.

      4. after you’ve activated the Add-on, change the drop-down on the Main Sheet to a different query, OR change the tickers in the green background of the BScompare tab

        A ‘loading…’ message should appear (replacing the #ERROR or #NAME? text), and you should see data populate cells and rows within 30 seconds, depending on network connectivity and the size of the result set queried.

      5. once you’ve confirmed the data is flowing to this file, try other Google Sheet resources linked at right

      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 API Access Add-on’s Authentication panel and Authorize the XBRL API. Once the Add-on is authorized, make a change to the Google Sheet 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 API access 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 API Access Add-on menu option to display the right-side panel and click the Authorize link. Once the Add-on is authorized, make a change to the Google Sheet to initiate a query.
         
      4. Open your copy of the ‘XBRL Spreadsheet’ template, choose the ‘Config’ tab and copy / delete / restore the URLs in cells B2 and B3, then return to the ‘Main Sheet’ tab and change the query in cell B1 (make sure Display is set to ‘Query Results’).
        This step breaks and resets valid uses of the =showData() function – you can also use search and replace to add a * in front of the initial = for all formulas in the Google Sheet (stopping the formulas), then reverse the find and replace to remove the * from the front of the formulas.
         
      5. move all copies of XBRL API Google Sheet templates to the trash and then make a new copy of XBRL Spreadsheet – https://xbrl.us/xbrl-api-spreadsheet.
         
      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.

    • #113573
      Natasha Pchelko
      Participant

      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

      • #113587
        David Tauriello
        Keymaster

        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.

    • #118034
      Ashutosh Pandey
      Participant

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

      • #118064
        David Tauriello
        Keymaster

        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)

    • #118102
      Ashutosh Pandey
      Participant

      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.

      • #118109
        David Tauriello
        Keymaster

        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.

    • #118499
      Ashutosh Pandey
      Participant

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

    • #119644
      David Tauriello
      Keymaster

      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.

    • #121967
      Kevin Donaghy
      Participant

      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

      • #121977
        Kevin Donaghy
        Participant

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

      • #122181
        David Tauriello
        Keymaster

        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.

    • #129657
      James McKinney
      Participant

      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.

      • #130034
        David Tauriello
        Keymaster

        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.

    • #143246
      Elmhurst
      Participant

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

      XBRL Error

      • #143279
        David Tauriello
        Keymaster

        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.

      • #143303
        Elmhurst
        Participant

        It worked. Thanks!

    • #143247
      Elmhurst
      Participant

      Here is the correct image:

      XBRL Image

    • #156160
      James McKinney
      Participant

      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.

      • #156181
        David Tauriello
        Keymaster

        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.

    • #179416
      Chinmay Laddha
      Participant

      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

      • #181116
        David Tauriello
        Keymaster

        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.

    • #185957
      morjanah
      Participant

      I could not get to work on Firefox

      • #185988
        David Tauriello
        Keymaster

        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.

    • #186140
      J C
      Participant

      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.

Viewing 13 reply threads
  • You must be logged in to reply to this topic.

Comment