Google Apps Script
Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width just does not budge.
The same is true when you use the
copyTo method in Google Apps Script.
copyTo does essentially the same thing as copy and paste within the spreadsheet UI.
It takes the range you are copying from and pastes it the range you are, erh…pasting it into.
This essentially takes all the values, formulas and formatting in the range and plonks it into the target location. Of course, without that much prized column width.
To add the column width we need to also include a separate
copyTo that adds the column width separately.
copyTo can take 3 arguments:
copyTo(destination, copyPasteType, transposed)
We can use the
copyPasteType to call an enumeration to get the column width of the selected range. So the second argument after the destination argument would look like this:
Enumerations are identified in Google Apps Script by their all-caps, cause they’re yelly.
So to copy and paste all the ranges formatting and column width, your code might look a little like this:
I have a selected range from a Stock Orders list I want to copy and paste across programatically to a new sheet. I want to keep all the formatting and, of course, the column widths.
Let’s take a look at the sheet I want to copy from.
I want to select the range from A1:G6 only and paste it into a new sheet.
Here is the code:
* Demonstrate Copy and Paste while retaining
* column width.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Weapons");
//Copy and paste with width and height Values
// Create a new sheet
var newSheet = ss.insertSheet("Copy with format");
// Get the range from the sheet you will copy from.
var weaponsRange = sheet.getRange("A1:G6");
// Get the range of the sheet that is your paste target.
var pasteRange = newSheet.getRange("A1:G6");
var columnWidths = SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS
var run = copyAllInRange()
I first set my global variables to get my spreadsheet (Line 9) and then get the sheet I am copying from (Line 10), in this case “Weapons”.
In line 13, I start my function,
copyAllInRange(). In this function, I first need to create a new sheet that I have named, “Copy with Format” (Line 15).
Next, I get the range I want to copy from the “Weapons” sheet (Line 17). In this case I am happy with the location of data, so I won’t alter the paste location. I then set the paste location of the
newSheet, aka “Copy with Format” the same as the “Weapons” sheet (Line 19).
On line 21, I create the variable
columnWidth and set the enumerator to
Then I copy data from the “Weapons” sheet range and paste it into the newly created “Copy with format” sheet range (Line 22).
Finally, it’s time to add the column width by calling the
copyTo method a second time, but this time including the the
columnWidth variable. This second call will only get the column widths of the data and paste it in the new sheet.
The result gives us this:
At last. Copy and paste with the width I want.