Common data formats are .csv, .json and .txt files.
For spreadsheets read Data Organization in Spreadsheets by Karl W. Broman & Kara H. Woo https://www.tandfonline.com/doi/full/10.1080/00031305.2017.1375989
Flatten Tool (json to csv) https://flatten-tool.readthedocs.io/en/latest/examples/
Google Sheets: App Script https://developers.google.com/apps-script and Google Query Language https://developers.google.com/chart/interactive/docs/querylanguage. And a library for version controlling: Clasp https://github.com/google/clasp
Power Query is an Extract, Transform and Load (ETL) tool built in to Microsoft Excel (from 2016) and Microsoft's Power BI. It has a user interface which allows users to transform data without needing to code, although coding is possible in the "M" language. https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query
Mistakes and open/closed methodologies: Austerity spreadsheet mistake https://www.theatlantic.com/business/archive/2013/04/forget-excel-this-was-reinhart-and-rogoffs-biggest-mistake/275088/