Copy-pasting big datasets manually into Google Sheets can quickly turn into a nightmare: slowdowns, crashes, weird formatting issues... you name it.
But guess what? With a little bit of Google Apps Script magic, you can automate the entire process, safely and efficiently.
In this guide, I’ll show you how to push large amounts of data into your sheet like a pro, without risking a browser meltdown.
(And don’t worry, I’ll keep it simple and copy-paste ready.)
In the left-hand panel, click the "+" next to Services, then search for and add Google Sheets API to your project.
Copy paste this snippet to define all the required variables:
const DRIVE_FOLDER_ID -> Folder Id where you want to store the CSV files
const SOURCE_SPREADSHEET_ID -> The spreadsheet Id, where the data to stransfer is stored
const SOURCE_SHEETNAME -> The sheetname, where the data to stransfer is stored
const TARGET_SPREADSHEET_ID -> The spreadsheet Id, where you want to transfer the data
const TRAGET_SHEETNAME -> The sheetname, where you want to transfer the data
This function grabs all the data from your source Google Sheet, cleans it up (removing annoying characters and formatting dates properly), and then exports it into a brand new CSV file inside the Google Drive folder you chose.
It also returns two handy things: the ID of the CSV file and the range of the data you exported (in case you need it later).
In short: it transforms your sheet into a clean CSV file, ready to be used, automatically!
This function wipes out all the existing values from the target sheet before pushing the new dataset.
This step is really important, especially if your script runs automatically and the size of your data can change.
Why? Because if the new dataset is smaller than the previous one, leftover rows from the old data could stay in the sheet, making your results messy or wrong.
Clearing first ensures the sheet is fresh and clean, every single time.
In short: it’s like wiping the whiteboard before you start writing again. 🧽
This step sends the fresh data from your newly created CSV file directly into the target Google Sheet.
It uses the CSV file we just generated and updates the sheet with its content, covering exactly the same data range as the original one.
Why it matters:
This ensures your target sheet always reflects the latest version of your dataset, nice, clean, and up to date!
In short: it's like pouring your fresh coffee into a clean mug, no leftovers, no mess. ☕✨
After pushing the data into the target sheet, you don't really need the temporary CSV file anymore.
This step moves that file to the trash to keep your Google Drive tidy and avoid cluttering it with old files every time the script runs.
Why it matters:
If you skip this step, your Drive will slowly fill up with useless CSVs, and trust me, it's easy to forget about them until it's a mess.
In short: it’s like throwing away the pizza box after you eat the pizza. 🍕🗑️
Now that all the pieces are ready, we tie everything together in one clean mainPushThisSheetToTarget function.
It handles the whole workflow step-by-step: exporting the data, clearing the target sheet, updating it, and optionally cleaning up the temporary file.
Why I recommend it:
I personally love having a "Main" function in my scripts, it keeps the logic clean, organized, and super easy to maintain.
Instead of digging through scattered code, you just look at the main flow to understand (or modify) what happens. Future-you will thank you. 🙏
In short: it’s like having the table of contents for your script, you immediately see what’s happening and in what order. 📚
Managing large datasets manually can quickly become a headache, but with this simple Apps Script setup, you can automate the entire process — cleanly, safely, and reliably.
Whether you use it for regular updates or one-off pushes, having a structured flow like this will save you a ton of time (and sanity).
Feel free to tweak it to your needs, and happy scripting! 🚀