In our team, we have a lot of Google Sheets 📃 and we often need to show the same content between sheets. (e.g. 2 Sheets both need data in tab Average Daily Order. One is for internal analysis, another is a report for leaders.) In this post, I’d like to share some common solutions to this problem instead of manually copy-pasting data (That’s too time-consuming…🥺)
- importrange
- AppScript: getValues + setValues
- AppScript: batchGet + batchUpdate
Our Goal
Displaying the same data in Original Data’s transaction tab to Backup Data
ImportRange
In most cases, importRange will work just fine. (FYI, here is a nice article sharing solutions to most importRange errors 👾)
=IMPORTRANGE("url_of_original_sheet","transaction!A:N")
Conditional Import
If we want to import specific columns or data fitting certain conditions, QUERY is a handy tool 🛠
=QUERY(IMPORTRANGE("url_of_original_sheet","transaction!A:N"), "select Col1, Col3, Col7")
=QUERY(IMPORTRANGE("url_of_original_sheet","transaction!A:N"), "select * where Col3='CC Payment'")
But when there is too many formula/data or having too many people editing the original sheet, importRange may not work 😥😕🤒 If stability is highly emphasized, we need to use AppScript 💎
AppScript
With AppScript, we can get data in one sheet, paste it in another sheet, then use trigger to specify its update frequency.
- Pros: Backup Sheet is *always alive* ✨
- Cons: Backup Sheet may not be real-time updated (Depends on how we set our trigger. We can notify ourselves when update fails 😉)
Let’s open the AppScript Project in our Original Sheet and get started 👉
Method ☝ getValues + setValues
In Code.gs, paste and modify the following code ✒
Now save 💾 and click Run to see if it works 🏃♀️🏃♂️
After execution completes, Sheet2 in Backup Data is then updated :-)
Let’s create a trigger to execute this function automatically ⏲ (Personally, I do not recommand setting triggers to run less than every 15 minutes. In the code above, we clear all data and paste data afterwards, so you may see data suddenly disappear for 0.5 seconds each time when running Backup function.)
Method ✌ batchGet + batchUpdate
Method 1 is good enough for most cases. However, when we are dealing with large data, setValues could become extremely slow and even timedout 🐢(AppScript limits each execution to run 6 minutes maximum.) Another reason to use batchGet/Update is when the data range is not continuous, method 2 will be a better choice 🤗
We need to add Google Sheet API first:
More usages of batchGet/Update can be found here.
Save and run the new function, we can see Sheet3 in Backup Data is now updated :D
We did not use copyTo in the example above because our task is to copy data to *different* GSheet. If you only need to copy-paste data within the same Gsheet, copyTo will do it for you 💗
