Sajid Ahmed
It’s simple to copy and paste values into Google Sheets. We can use the keyboard shortcut and other commands for doing so. But there is a distinctive feature in Google Sheets like the Apps Script. We can use this extension to insert some codes and perform amazing things. For instance, I can automatically copy and paste values through it. If I want Google Sheets to complete the task at a specific time, I could also add some Triggers in our code. So, in this article, we’ll see 4 useful ways to copy and paste values using Apps Script in Google Sheets with clear images and steps. And lastly, we’ll get an output like this from our article.
A Sample of Practice Spreadsheet
You can download Google Sheets from here and practice very quickly.
4 Useful Apps Scripts to Copy and Paste Values in Google Sheets
Let’s get introduced to our dataset first. Here we have some products in Column B. Now we want to copy and paste these products into another column automatically without using any command or function. We can do that by using the Apps Script extensions. So I’ll show you 4 useful ways to copy and paste values using Apps Script in Google Sheets by using this dataset.
1. Copying and Pasting Manually
We can copy and paste values manually by using Apps Script in Google Sheets. Here we just have to run the script code again and again whenever we want to copy and paste any values. Let’s see the steps.
Steps:
- Thirdly, insert the following code there-
function PasteValue(){
var cp = SpreadsheetApp.getActiveSpreadsheet();
var copypaste = cp.getSheetByName("Dataset 2");
copypaste.getRange("B4:B").copyTo(copypaste.getRange("D4:D"),{contentsOnly:true});
}
- Then, rename the script as “Copy and Paste” and the file as “Manual”.
- Next, click on the Save button and press Run.
Code Breakdown
- Here we have used only one function called PasteValue.
- This function directly copies any values in the given range from Column B and from the given sheet which is Dataset 2.
- Then it pastes the values in the given range of Column D in the same sheet.
Read More: How to Paste Space Separated Values in Google Sheets (2 Ways)
2. Generating Copy and Paste Button
Apart from the previous method, we can also generate a copy-and-paste button using Apps Script in Google Sheets. The advantage of this method is that we don’t have to run the code repeatedly. If we click on this button, the Apps Script will automatically copy and paste our desired values. Let’s see how to do it.
Steps:
- First, give the sheet a new name, “Dataset 3”.
- Go to Extensions > Apps Script next.
- Then, put the code in the code box-
function onOpen(){
var cp = SpreadsheetApp.getActiveSpreadsheet();
var entry = [];
entry.push({name: "Paste", functionName: "PasteValue"});
cp.addMenu("Copy", entry);
}
function PasteValue(){
var cp = SpreadsheetApp.getActiveSpreadsheet();
var copypaste = cp.getSheetByName("Dataset 3");
copypaste.getRange("B4:B").copyTo(copypaste.getRange("D4:D"),{contentsOnly:true});
}
- Moreover, rename the script and file to “Copy and Paste” and “Generating Button” respectively.
- Next, select the Save option.
- Select onOpen once again then hit Run.
Code Breakdown
- Here we have used 2 functions called onOpen and PasteValue.
- The onOpen function creates a new menu in the current sheet called Copy and a submenu under it called Paste.
- The PasteValue function works the same as method 1.
- In the end, you’ll notice that Column D now has all of the data from Column B.
Read More: [Solved!] Paste Values Is Not Working in Google Sheets (2 Fixes)
3. Automatically Copy and Paste at Specific Time
Now, we’ll further extend our previous example. We want to copy and paste values at a specific time say every week automatically. In order to do that we have to add some Triggers to our Apps Script code. This Trigger will enable our code to run automatically and produce updated results every week at a specific time.
Steps:
- First and foremost, open the Apps Script window like the first step of the first method.
- Then, insert the following code there-
function PasteValue(){
var cp = SpreadsheetApp.getActiveSpreadsheet();
var copypaste = cp.getSheetByName("Dataset 2");
copypaste.getRange("B4:B").copyTo(copypaste.getRange("D4:D"),{contentsOnly:true});
}
- After that, name the script “Copy and Paste” and the script’s file “Manual”.
- Next, select the Save button and then press Run.
- Then, go to Triggers under the Apps Script window.
- Next, click on the Add Trigger button at the bottom side of the Trigger window.
- Add Trigger for Copy and Paste window will open.
- Then, choose PasteValue under Choose which function to run menu.
- After that, scroll down for more options.
- Thereafter, select Time-driven under the Select event source menu.
- Then, choose the Week Timer because we want to copy and paste values every week.
- After that, we pick Sunday as our day and gave the time from 10 am to 11 am. The time depends on your Google Sheets timing.
- Lastly, click on the Save button.
- At last, you’ll notice that every Sunday between 10 and 11 am, all the data from Column B are automatically pasted into Column D.
Read More: How to Paste Comma Separated Values in Google Sheets
4. Copying and Pasting to Another Sheet
Sometimes we need to copy some values from a sheet and paste it into another sheet in the same Google Sheets. We can also do that by using the Apps Script code. Below we’ll see the process.
Steps:
- Earlier on open a new sheet in the same Google Sheets and rename the sheet as “Dataset 5”.
- Then, go to Extensions > Apps Script.
- Next, type the following code there-
function sendDataToDestinationSpreadSheet() {
var currentSheet = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = currentSheet.getSheetByName('Dataset');
var mainRange = mainSheet.getRange('B4:B');
var mainValues = mainRange.getValues();
var targetSheet = currentSheet.getSheetByName('Dataset 5');
for(var u = 0; u < mainValues.length; u++) {
var firstcolumn = mainValues[0,u][0];
targetSheet.getRange(u+4, 2).setValue(firstcolumn);
}
}
- Again, change the script’s name to “Copy and Paste” and the file’s name to “Another Sheet”.
- Consequently, select Run after selecting the Save button.
Code Breakdown
- Here the function name is sendDataToDestinationSpreadSheet.
- This function directly copies any values in the given range from Column B and from the current sheet which is Dataset.
- Then it pastes the values in the given range of Column D in the new sheet called Dataset 5.
Conclusion
That’s all for now. Thank you for reading this article. In this article, I have discussed 4 useful ways to copy and paste values using Apps Script in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.
