Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.

Paste without column widths.

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.

copied Google Sheet
When you want to copy and paste this…
Paste without column widths.
…but you get this.

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.

copyRange.copyTo(pasteRange);

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:

SpreadsheetApp.copyPasteType.PASTE_COLUMN_WIDTHS

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:

The Example

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.

copied Google Sheet

I want to select the range from A1:G6 only and paste it into a new sheet.

Here is the code:

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 PASTE_COLUMN_WIDTH.

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:

Copy with format and column width in Google Sheets

At last. Copy and paste with the width I want.

5 thoughts on “Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.”

  1. Hi Yagi, thank you for sharing the post. I was looking something similar to your App Script. I have created itinerary costing and I need to copy the range every time when I need to do costing. It would be so much easier if I can copy selected range of rows and paste to blank row underneath what is already there in the same sheet. How can I achieve this? Your support will be highly appreciated.

    1. Hi Raj,

      Good question. To get the first empty space at the bottom of the sheet you will paste into you will need to do something like:
      var firstBlankSpace = sheet.getDataRange().getLastRow() + 1;
      Where ‘sheet’ is the name of your sheet.

      Cheers,

      Yagi.

      1. Hi Yagi, many thanks for your response. I would really appreciate if you write me complete code to copy to new blank rows underneath.
        Cheers!

        1. Hi Raj, I really think you have all the tools you need to write your own code for this in the tutorial and the extra information I provided. Don’t be afraid to make several attempts and always refer back to the Google App Script documentation. You’ll learn a lot by doing this and, I am sure, will be delighted with the results of your hard work.

          If you make a solid attempt and are still stuck, feel free to post your attempt below in the comments and I or one of the readers may be able to help guide you.

          Happy coding,

          Yagi.

  2. Hi Yagi, thank you for such an inspirational response. I know you want me to learn something, and me too, I always learn the way you suggest but the problem is I need it urgently so I asked u to get help quickly. However, I will give it try and come back to u if any problem. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *