How to Copy Values Instead of Functions in Google Sheets

Learn how to effectively copy values from one Google Sheet to another without transferring functions using Google Apps Script.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: copyTo(target) copies my functions but need it to copy values

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Copy Values Instead of Functions in Google Sheets

Google Sheets is a powerful tool, enabling users to handle data efficiently using various functions and formulas. However, there are times when you may want to create a backup of your data without including those formulas and just keep the static values. This can be particularly vital when you need to create backup sheets for data that may later be changed or relied upon, where formulas can lead to issues when referencing different data.

In this guide, we will explore how to adapt a Google Apps Script to copy values from one sheet to another effectively, avoiding the common pitfall of transferring functions.

The Problem at Hand

In the original question, the user mentioned an issue with using the copyTo() method. Instead of getting the desired values transferred over, the script was copying the functions as well. The provided code created a copy of the sheet but didn't maintain static data.

Here's a quick recap of what the initial attempts looked like:

[[See Video to Reveal this Text or Code Snippet]]

Although this did create the new sheet, the need was to preserve values only, not formulas.

The Solution: Copy Values in Google Sheets

To achieve the goal of copying only values, we will need a different approach. Rather than using copyTo(), we can manually extract the values from the source sheet and insert them into the target sheet. Below is a step-by-step breakdown of what the solution entails.

Step-by-Step Breakdown

Access the Active Spreadsheet: Start by getting a reference to the current spreadsheet.

Retrieve the Source Sheet: Identify the specific sheet you want to copy from (in this case, "AHA").

Extract Values: Use getDataRange().getValues() method to get all the values in the sheet.

Open/Access the Target Spreadsheet: Access the target spreadsheet where you want the values to be copied.

Insert a New Sheet: Create a new sheet in the target spreadsheet using the desired name.

Set Values: Finally, use setValues() method to paste the values extracted from the original sheet into the new sheet.

Example of the Complete Code

Here's the complete script to copy values:

[[See Video to Reveal this Text or Code Snippet]]

Key Points to Note

getValues() Method: This retrieves only the static values from the cells without any of the formulas.

Insertion of New Sheet: The insertSheet(name) method creates a new sheet by the given name, making sure you don’t overwrite any existing sheets.

Setting Values: The setValues() function allows you to paste in the array of values, starting from cell (1,1) of the new sheet.

Conclusion

Copying values instead of functions can simplify the data management process. Utilizing above steps and code, you can effectively create backups or secondary versions of your data that are not dependent on the original functions. Remember to replace 'YOUR_SHEET_ID' with the correct ID of your target spreadsheet to ensure the script runs smoothly.

Feel free to test this out in your Google Sheets, and you'll be able to keep your data safe with just the values you need!