Automatically email your database data as a spreadsheet with no code

Do you constantly find yourself exporting data from your database as a spreadsheet? And then emailing it to everyone on your team? Every. Single. Day.

Well, those days are long gone. In this article, we will walk through how to automatically send an email containing a spreadsheet of your database data with very little code.

Connect to a database

First, sign into your APISpreadsheets account and open the Database page from the main menu.

NOTE this is a paid feature. BUT, feel free to reach out on sales@apispreadsheets.com to try out this feature for free. We will set you up with a 14-day free trial.

Click the Connect a new database button to set up a connection.

This will open a window where you'll have to enter database credentials.

First, select the type of database. APISpreadsheets currently supports MySQL and PostgreSQL databases.

Provide credentials by filling in ‘Database Name’, ‘Port’ and ‘Hostname’ fields.

If the database is password protected, enter ‘Username’ and ‘Password’ values as well.

Finally, enter the description of the database. Mention the purpose and important details about the database.

Note: Enter hostname value without http prefix, starting or ending slashes.

Click the Connect button to finish connecting the database.

Select data from your database

Once you’ve successfully connected the database, APISpreadsheets will redirect you to the database settings page.

Clicking the Add a New API button will open a form.

First, enter a descriptive name for your API.

In the code block, write the query that will select the data to be emailed as a spreadsheet

You can click View DB Tables & Columns to help you write the query

You can also Read query rules to see some examples of valid queries that will be able to select data from the database.

Example query and data

As a demonstration, let’s write a query to select movie data that will then be emailed as a spreadsheet.

There is a table in our database called movies that has four columns: title, genre, director and release_year

Let's say in the report that we want to send, we only want to include the title column

For this, we enter a simple query with the following results:

You can click the Test SQL Query button to check if the query selects data as it should.

If the query is working correctly, click the Save API button.

Configure Auto Email Settings

Once your query is saved, you will be redirected to the File page of this API.

On this page, click on the Auto Reporting menu bottom

This will open the settings that you will need to configure to automatically send the data selected from the query as a spreadsheet

There are 3 main settings to configure

1. Date & Time

Select which days and what time you wish the email to send. You also need to select the timezone

2. Email Message Details

Enter the Subject, the Message, and the Name from which the email should appear from

3. Send To

Enter ALL the emails that should receive this email with the spreadsheet

After configuring the settings, click on the Set up Auto Reporting button

Receive the email with the spreadsheet

Now, everyone you specified in the Send To section will receive an email with a spreadsheet containing the data obtained from your query at the time of the email

The best part is that as the data changes in the database, the spreadsheet will contain the most up to date results at the time of receiving it!

Thanks for reading! Let us know if you have any questions by emailing us at info@apispreadsheets.com