Automatic export from QuickBase into Excel

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Hello!

There are a few parts to this project I'm trying to complete. First, I have specific filtered table reports I've created in QuickBase in multiple different tables. I'd like to automatically export the filtered reports into specific Excel sheets in a workbook.

For example, I have a table called "Clients" where I've created the report, "Active Clients". I also have an Excel Workbook called "Fact Sheets" with multiple different tabs (sheets). One of the worksheets is called "Clients". I want to export my Active Clients report into the Clients worksheet.

Is this possible? I've been trying to read up on the API guide (https://www.quickbase.com/api-guide/index.html) but it doesn't sound exactly like what I'm looking for.
Photo of Anna

Anna

  • 24 Points

Posted 6 years ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 886 Points 500 badge 2x thumb
I would suggest finding an Excel expert. I am not sure but I believe you can use the "From Web" wizard or possibly use the embedded VBA in Excel to employee the QuickBase API to import QuickBase data to a sheet in Excel.
Photo of Anna

Anna

  • 24 Points
Thank you! I'll try both suggestions and see how it goes. :)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
You can do a "Web Query" from Excel. Under the Data tab select "From Web" (displayed on the far left) and browse to your QuickBase table and you are off to the races.
Photo of Juan

Juan

  • 476 Points 250 badge 2x thumb
Hi Dan, I've implemented this approach successfully and I'm able to obtain the data I need. The only issue I'm facing now is that from time to time my data connection seems to "forget" my QuickBase credentials and the data download fails, until I go into Connection Properties and click on "Edit Query".  When I do this, Excel attempts to open the QuickBase report in a browser window within the Edit Query dialog box and asks me to enter my QuickBase credentials. Once I re-enter my credentials, the data download executes successfully.

I've attached a screenshot of the Edit Query dialog box.



I don't want to have to do this every time. Is there a way to store the credentials within the connection string?

Thanks in advance.

Juan
(Edited)
Photo of Anna

Anna

  • 24 Points
Thank you for your response! I tried to do this, and it was almost exactly what I wanted. Unfortunately, the output was a bit more complicated than I have the knowledge for. So the short answer is that this would be able to work for anyone trying to do this, but I don't think it would work for me.

The long answer (if anyone is interested), is that I need the data of the table that I pull to appear in column/row A1. When I try to pull over the report from QuickBase, it seems to carry over much more information than is visible, including all 6 different types of reports and descriptions (i.e. "Table; Show your data in a spreadsheet-style report with rows and columns.") as well as the filters of the report and the summary of how many records are available. Another thing that doesn't work for me is that I have some multi-line text fields that show up in one cell per line; I need all lines to appear in one cell.

Thank you for your help!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Read this article:

http://www.kimgentes.com/worshiptech-web-tools-page/2010/8/19/web-connecting-csv-files-as-external-data-to-excel-spreadshe.html

The URL you would use would be a CSV report using either a report with this setting:

Options | Format = Comma-Separated Values

Or a URL that calls API_GenResultsTable

https://YOURSUBDOMAIN.quickbase.com/db/YOURDBID?act=API_GenResultsTable&qid=1&options=csv
Photo of Anna

Anna

  • 24 Points
This is really helpful! I'll play around with this and see if it fits my needs. If not the project I'm working on, definitely another one I'm involved in. Thank you!
Photo of M

M

  • 80 Points 75 badge 2x thumb
Anna,
were you able to figure out how to get [Field 1] to pull into Cell A1 in Excel, and [Field 2] to pull into Cell B36 (or whatever other cell you wanted it to go into?
Photo of Anna

Anna

  • 24 Points
Hello!
Thank you for following up. I don't think I was able to do exactly what I wanted, but it turned out that the group I was working with decided to go a different direction with the project that would benefit them more in the long run. We'll see...I may need to open this topic back up in the future, but for now I think everything is all set.

Thank you!
Photo of Joshua

Joshua

  • 0 Points
https://YOURSUBDOMAIN.quickbase.com/db/YOURDBID?act=API_GenResultsTable&qid=1&options=csv


This looks like something I could use....What would be the values I need to replace in the above line...?  "YOURSUBDOMAIN" I would replace with my sub domain...
What about "YOURBID?" what would replace this?
Where would I put the table name?

Sorry...new to trying this, but this would make the whole export table go much better.