After you have accessed your data on Google Sheets, you will have to transform it based on two main factors:
- The limitations of the database that you will load the data into
- The type of analysis that you want to perform
Each system has specific limitations on the data types and data structures that it supports. If, for example, you want to push the data into Google BigQuery, then you can send nested data in a JSON format directly. However, you must keep in mind that the data you get from Google Sheets is in the form of a tabular report, just like a CSV.
Of course, when dealing with tabular data stores like Microsoft SQL Server, this is not an option. Instead, you will have to flatten out your data before loading it into your database.
Another consideration is that you have to choose the right data types. Again, depending on the system you will send the data to and the data types that the API exposes to you, you will have to make the right choices. These choices are important because they can limit your queries' expressivity and limit your analysts on what they can do directly out of the database.
Google Sheets has a very limited set of available data types, meaning that your work to do these mappings is much easier and straightforward.
Each table is a collection of columns with a predefined data type as an integer or VARCHAR. PostgreSQL, like any other SQL database, supports a wide range of different data types.
A typical strategy for loading data from Google Sheets to PostgreSQL is to create a schema where you will map each API endpoint to a table. You should map each key inside the Google Sheets API endpoint response to a column of that table, and you should ensure the right conversion to a Postgres-compatible data type.