Irakli Tchigladze
on 2022-08-14
All SaaS tools worth their weight need to allow their users to upload spreadsheets and CSV files. You can do this by setting up a spreadsheet importer. Read our article on best spreadsheet importers.
But what if you want to use the data from a user uploaded spreadsheet right away. Maybe you want to use the spreadsheet data to create charts, tables, text, or perform more complex tasks, like scheduling order fulfillment.
Let’s take a look at this typical Excel spreadsheet:
Uploading a spreadsheet to APISpreadsheets importer will generate an API URL where web applications can access spreadsheet data.
Once the spreadsheet is uploaded, APISpreadsheets will return the file information. Most important is the URL of the API that shares spreadsheet data.
You can make a call to the API and display data, create infographics, tables, or generate content using the data.
Let’s imagine we have a dashboard with a graph that charts total sales for our company.
Creating a spreadsheet importer will give us the code snippet to PASTE into our website, which will allow every salesperson to submit their own sales and immediately see a comparison:
In the process of uploading a file, the user needs to set up column mapping. This helps APISpreadsheets ‘translate’ spreadsheet data to JSON format, which will be shared over API.
As long as the uploaded spreadsheet contains enough information, you can use data to generate any type of content, from visual graphs to product listings.
You can try this feature with a free APISpreadsheets account.
1. Open the ‘Importer’ section in the main menu to create an importer and get the code for the button.
2. Name your spreadsheet importer
Once we create the spreadsheet importer, we need to specify the structure of spreadsheet data:
In the left sidebar menu, open the ‘Column’ section.
1. Assign values in the ‘Month’ spreadsheet column to the ‘month’ object property.
2. Map values in the ‘Sales’ spreadsheet column to the ‘sales’ object property.
3. Set other settings: checkbox to make column values required (or not), and select the type of values in the column.
4. Click the green ‘Save Column’ button to save changes.
Each spreadsheet importer contains a code snippet you can COPY and PASTE into the HTML of your page.
Inserting the code will display a button which allows users to upload spreadsheet files.
Within a second, data contained in that spreadsheet will be widely available for consumption by any application.
1. Go to the ‘Code’ section
2. Copy the snippet manually or using the little green button.
3. Paste the code into your HTML to display a button.
Uploading a file will trigger the ‘importComplete()’ function, which returns important information about the spreadsheet, such as API that contains its data:
In the console you can find important file details, such as API URL:
You can use styles and classes to customize the appearance of the button to match the design of your website.
Let’s take a look at a live example - a dashboard that contains a chart of company sales.
The 'Submit Sales Numbers' button is connected to a spreadsheet importer, which allows visitors to upload their own sales to compare them with overall company sales.
Before uploading an Excel spreadsheet:
After uploading an Excel spreadsheet:
1. Click the ‘Submit Sales Numbers’ button
2. Select the file to upload or drop it in the uploader
3. Select the row that contains column labels.
4. Map spreadsheet labels to previously defined columns.
5. Click ‘Save Column Mapping’ to finish. You can use the closeImporter() function to automatically close the window once columns are mapped
We have discussed an example of a dashboard, where users can upload their own sales.
You can see a live sample on this link. You can click the ‘Open Sandbox’ button in the bottom-right corner to see the source code.
Let’s take a look at the function that is executed once the user uploads a spreadsheet:
Once the upload is complete, the spreadsheet importer returns information about the file.
To keep things simple, we store the entire object in the fileInfo variable.
We read the apiURL property of the fileInfo object and store it in the apiURL variable. We need this value to make a call to the API and receive spreadsheet data.
The columnNames property contains information about column labels in the spreadsheet. We will store these to create labels for the chart.
We need to request spreadsheet data from API URL
First, we set an if condition to properly format the URL of our private API, if it is private.
Second, we provide an API URL to the fetch() method to receive spreadsheet data in JSON format.
We use the .json() method to parse the data.
In this final step, we use the anychart library to visualize the data.
We create a data variable that contains two sets of data: one for the entire company, and one for individual sales data, found in the uploaded spreadsheet.
We create the chart by writing anychart.column() and store it in the chart variable.
Then fill it with data by using the data() method on the variable.
We can use the title() method to name the chart.
The .container() method can be used to specify the container element where the chart should be drawn.
Finally, we call the draw() method on the chart variable to draw the chart.
API Spreadsheets can help small-to-medium size businesses' instantly exchange spreadsheets and important information contained in them.
In this article we showed how you can create an API that shares spreadsheet data in just a few steps and minimal coding knowledge.
