|

Copy Data with Conditions in Apps Script

Byautooffice

This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”. Added a strikethrough when editing what AI has written, and added color when edited by author

Table of Contents

  1. Summary
  2. Code
  3. Code Explanation
  4. Example
  5. AI Prompt

Summary

This blog post will demonstrate a script that copies data with specific conditions in Google Apps Script. The script copies data from “시트2” sheet to “시트4” sheet in the same spreadsheet.

Code

Code Explanation

The code defines a function named copyDataWithConditions. It retrieves the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet(). Then, it gets references to the “시트2” and “시트4” sheets using getSheetByName().

The getDataRange() method is used to get the range containing all data in “시트2” sheet. The copyTo() method is then called on this range, specifying the destination range in “시트4” sheet as A1. The third argument, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, determines the type of paste operation to be performed.

There are 8 types of paste operation as below, See Google description link.

  1. PASTE_NORMAL: Paste values, formulas, formats and merges.
  2. PASTE_NO_BORDERS: Paste values, formulas, formats and merges but without borders.
  3. PASTE_FORMAT: Paste the format only.
  4. PASTE_FORMULA: Paste the formulas only.
  5. PASTE_DATA_VALIDATION: Paste the data validation only.
  6. PASTE_VALUES: Paste the values ONLY without formats, formulas or merges.
  7. PASTE_CONDITIONAL_FORMATTING: Paste the color rules only.
  8. PASTE_COLUMN_WIDTHS: Paste the column widths only.

Example

Let’s say we have a spreadsheet with two sheets: “시트2” and “시트4”. “시트2” contains a table with data, and we want to copy this data to “시트4”. We can use the copyDataWithConditions() function to accomplish this.

After running the script, all the data from “시트2” will be copied to “시트4” starting from cell A1.

AI Prompt

Write a function that copies data from “시트2” to “시트4” in the same spreadsheet using Google Apps Script. Set CopyPasteType as NORMAL. 

Similar Posts