Irakli Tchigladze
on 2022-08-02
APISpreadsheets allows you to upload your spreadsheet and use it as an API interface.
In this tutorial, we’ll show you how and why you should use SQL queries to selectively receive data from the API.
Log into APISpreadsheets and go to the ‘Upload’ section, where you can upload your file.
To keep things simple, let’s say we have an excel spreadsheet with information about movies:
Simply drop the file into the box and it will start uploading.
Once the file is uploaded, you will be redirected to the ‘File’ page for that spreadsheet.
On this page, you’ll gain access to API URLs to Create, Read, Update, or Delete data in your spreadsheet.
Navigate to the ‘Read’ section and enable the ‘Query data’ setting to apply an SQL query to the URL.
If you visit the API URL, it will return all the data from the uploaded spreadsheet.
This is a powerful feature that facilitates easy sharing of information and data.
If you look closely, you’ll notice that API URL contains an SQL query at the end:
SELECT * FROM GPNSQzYbLaT52U3I
This is the most basic query, which selects all columns from the uploaded file.
‘GPNSQzYbLaT52U3I’ is the file ID, which can be found at the top of the ‘File’ page:
You can also write a query to return values only in the specified column.
SELECT title FROM GPNSQzYbLaT52U3I
If you visit this modified URL, it will only return values in the title column.
APISpreadsheets also allows you to use WHERE statements to filter the data returned by the API.
SELECT * FROM GPNSQzYbLaT52U3I WHERE title="Lady Bird"
The SELECT statement returns all columns, but we can chain the WHERE statement to specify conditions for one of the columns in the table.
In this case, API will only return the movie with the specified value in the title column.
APISpreadsheets allows you to use SELECT, FROM and WHERE statements. Other SQL features such as ORDER BY and GROUP BY can not be applied to the API URL.
Turn on the ‘Query data’ toggle to apply SQL query to your API.
The ‘Count Rows’ feature allows you to output the number of rows that satisfy the condition set in the SQL query.
If there is no query, then the overall number of records in the spreadsheet.
SELECT * FROM GPNSQzYbLaT52U3I
In this query, we SELECT all columns and rows.
If we turn on the ‘Count Rows’ toggle and go to the URL, we’ll see the total number of rows:
This may be beneficial when you want to display the total number of businesses listed in your spreadsheet and similar use cases.
The third configuration option is the ‘Rows’ input field. If you enter a number in this field, it will limit the number of rows in the output to that number.