Context: Data analysts around the world π, handle massive amounts of data to derive meaningful insights for their organization π. Among the tools they use, Google Sheets π stands out due to its ease of use, accessibility, and collaborative features. However, many analysts have identified a recurring pain point: the cumbersome process of importing CSV files into Google Sheets repeatedly.
A typical week of an analyst in an e-commerce company π involves receiving multiple CSV files π containing sales, inventory, customer feedback, and more. The data from these files needs to be meticulously analyzed and presented in the companyβs weekly meetings. However, instead of diving directly into analysis, most analysts need to spend an inordinate amount of time just importing and structuring these CSV files into Google Sheets β³. This repetitive, time-consuming task reduces the efficiency of these professionals and delays the extraction of crucial insights π«.
So, I've made a CSV Importer for Google Sheets that lets users drag and drop CSV files onto the Google Sheet. The moment they drop the CSV file, they can select which columns to import ποΈ.
Features:
- Importing to Sheet β After validation and mapping, data will be populated to chosen google sheets, either appending to existing data or creating a new sheet π₯π.
- Optimization for Large Files β Large datasets are common in analytics. This solution will effectively handle large CSV files (~15MB CSV file) without causing performance issues or prolonged waiting times ππ¦.
Things to be done before running this all-new Jugaad app:
- Go to https://console.cloud.google.com/welcome
- Create a new project
- Select "APIs & Services" > "Library" from the left-hand sidebar. In the "Library" page, use the search bar to search for "Google Sheets API" and enable it
- Go to the credentials section and click on Create credentials.
- Click on Service Accounts and configure it. After it is done, you will get a mail and the creds.json file downloaded.(Store that creds.json file π, it is very very very very much needed)
Your spreadsheet pre-works(bear with this please, this is a one-time work π€¨):
- Go to the spreadsheet where you are working.
- Click on Share and paste the service email in "Add people or groups" section and give it editor access(uncheck notify people), so that the sheets can be mainpulated.
- Tadaaaaaπ₯³ all the preworks are done!!
- Firstly, download all the code files and open it in a Node.js Environment (just do npm install and refer google for the setup)
- Now, go to the index2.js file, there you will find creds.json file path, replace it with your creds.json path.
- Open terminal and type nodemon index2.js
- There you gooooooπ₯³, jugaad web app
- Now drag your .csv file, give your spreadsheetID, range , select the columns which you have to import AND BOOOOOOOOM,
- Your spreadsheet will be filled with dataaaaaaπππ
- Guess what, this app is very much efficient as it can handle ~15MB csv files as well πͺ.