Filling spreadsheet columns with data from database

perlancar has asked for the wisdom of the Perl Monks concerning the following question:

Imagine this scenario: you are given an Excel/LibreOffice spreadsheet (but increasingly now collaborating on a Google Sheet) where some columns are already filled, particularly a column containing row ID or primary key of some sort (e.g. client_id). Then, you have some other columns that you fill manually (e.g. audit_note). Finally, you also have yet some other columns that you need to fill from database (e.g. client_email, client_phone) so this sheet can be given to the sales person to follow up.

Currently my solution to fill the columns is the mysql-fill-csv-columns-from-query script. The flow is:

1. I select all cells on the Google Sheet, paste to terminal or a text file and save it to tmp1.tsv, then run something like:

% mysql-fill-csv-columns-from-query DBNAME --tsv tmp1.tsv 'SELECT c.email AS client_email, c.phone AS client_phone FROM clients c ... WHERE c.id=$client_id' > tmp2.csv

The script basically takes the SQL, replaces $xxx parts with value of corresponding CSV field, runs the query, fetches a single row, then adds/sets CSV fields from the SQL SELECT fields. It does this for every CSV row.

2. I then open tmp2.csv using LibreOffice then copy-paste the columns to the Google Sheet.

It is very much still bearable, but I am wondering whether there is an easier flow, preferably where Perl is still involved somehow.

Comment on Filling spreadsheet columns with data from database

Replies are listed 'Best First'.
Re: Filling spreadsheet columns with data from database by Lotus1 (Vicar) on Apr 23, 2019 at 17:11 UTC
How about automating the entire process? The Perl module Net::Google::Spreadsheets::Spreadsheet seems like a possible resource for automatically getting the cells from the Google spreadsheet. I haven't used it so I'm not sure if it's up to date. Start with a simple test version of what you need to do and build it up to the requirement you described. For your more complicated requirements create other versions. Through experience and refactoring you could perhaps combine the scripts at some point. See also: API::Google, Net::Google::Spreadsheets::V4, and Net::Google::Spreadsheets::Worksheet	[reply]
Re^2: Filling spreadsheet columns with data from database by perlancar (Hermit) on Apr 24, 2019 at 04:42 UTC
As for "automating the entire process", the query will mostly be different for each spreadsheet so that can't be automated. And the filling up will be a one-time process for each spreadsheet, so automating it does not benefit much. Using Google Spreadsheet API has crossed my mind to avoid the round-trip of copy-paste to TSV. Don't know how much of a hassle it would be though, anybody tried before? It would really be nice if I can just run a script on the terminal, enter the address/URL of the Google Sheet, the query, and voila, the columns will be filled by the script. No copying and pasting required.	[reply]
Re: Filling spreadsheet columns with data from database by mikelieman (Friar) on Apr 23, 2019 at 12:37 UTC
Myself, I would avoid the templating, and use a database table keyed on client-id. Load data from the exported spreadsheet. Merge with existing records or create new records as needed. Update as needed from existing database. Then export the new csv file.	[reply]
Re^2: Filling spreadsheet columns with data from database by perlancar (Hermit) on Apr 23, 2019 at 12:59 UTC
The actual SQL could be more complex, e.g. several joins and subselects. And I'd like to be more general: there might be cases when the primary key is not specified in the sheet's columns.	[reply]