In general, we use Google Sheets or Excel when we need to organize or use data. However, once data starts accumulating, we encounter more errors and it becomes harder to manage data.
So teams with large amounts of data usually use databases. This is because databases provide necessary security and remove data redundancy, as well as being able to use them to develop and use it for data-heavy tasks.
However, using databases proplery requires knowledge and skills, making it difficult for non-programmers to access and make the best use out of them at work.
Wouldn't it be great being able to work on Google Sheets, but also save the updated data directly to a database (MySQL)?
Sync Google Sheets and MySQL in 3 steps
You can sync Google Sheets and MySQL data in 3 steps at Outcode.
Tip: You can use PostgreSQL or MongoDB instead of MySQL.
1. Delete the existing MySQL data
(You can skip this step if it is your first time using MySQL and you don’t have any data in it.)
It’s very simple to delete the existing data. Type in the query as shown below.
DELETE FROM payment
Remember that we are deleting the data so we can save only the newest Google Sheets data into the database.
2. Bring the data you want to save in MySQL from Google Sheets
In order to bring data from Google Sheets, click as shown below. Make sure to bring all data from the sheet you want.
3. Save the data from #2 in MySQL
You need the query below to save the Google Sheets data in MySQL.
INSERT INTO payment
(company_id, name, headcount, year_founded, email, rep_name, Phone_number, modify_date, inactive_req)
values ('{{b.ColumnA}}', '{{b.ColumnB}}', {{b.ColumnC}}, {{b.ColumnD}}, '{{b.ColumnE}}', '{{b.ColumnF}}', '{{b.ColumnG}}', '{{b.ColumnH}}', '{{b.ColumnI}}')```
What you see after INSERT INTO are the row names and next to VALUES, you can see the order of the rows.
If you look at the query closely, you see that some parts have single quotation marks ('{{Column}}') and some don’t. This is to ensure data like texts, dates, or time, other than numbers to be saved correctly.
& more!
If you want to sync your Google Sheets and database, get started here!