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

Google Apps Script: SpreadsheetApp, copyTo

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.

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:

copyRange.copyTo(pasteRange);
copyRange.copyTo(pasteRange,
      SpreadsheetApp.copyPasteType.PASTE_COLUMN_WIDTHS, 
      false);

copyRange.copyTo(pasteRange);

copyRange.copyTo(pasteRange,

SpreadsheetApp.copyPasteType.PASTE_COLUMN_WIDTHS,

false);

The Example

I have a selected range from a Stock Orders list I want to copy and paste across programmatically 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.
  *
  */

//***Globals***
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Weapons");

//Copy and paste with width and height Values
function copyAllInRange(){
  // 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
  weaponsRange.copyTo(pasteRange);
  weaponsRange.copyTo(pasteRange,columnWidths,false);

};

var run = copyAllInRange()

/**

* Demonstrate Copy and Paste while retaining

* column width.

//***Globals***

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Weapons");

//Copy and paste with width and height Values

function copyAllInRange(){

// 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

weaponsRange.copyTo(pasteRange);

weaponsRange.copyTo(pasteRange,columnWidths,false);

};

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

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

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

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Finally, it’s time to add the column width by calling the copyTo method a second time, but this time including 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.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

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

Raj says:

at 11:27 am

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.

Loading...

Reply
1. Yagi says:
  
  at 3:37 pm
  
  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.
  
  Loading...
  
  Reply
  1. Raj says:
    
    at 4:01 pm
    
    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!
    
    Loading...
    
    Reply
    1. Yagi says:
      
      at 4:16 pm
      
      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.
      
      Loading...
      
      Reply
Raj says:

at 6:53 pm

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!

Loading...

Reply

Hi Yagi,

I have this code below but when I run it, it keeps copying a content in column A, I want it to be in A,B,C,D (I don’t need to keep the format).

What am I doing wrong?
Thank you!

function copyInfo() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var copySheet = ss.getSheetByName(“Test”);
var pasteSheet = ss.getSheetByName(“Sheet5”);

var columns_to_be_copied = [‘B’, ‘L’, ‘M’, ‘V’];
var columns_to_be_pasted = [‘A’, ‘B’, ‘C’, ‘D’];
for (column in columns_to_be_copied){
var copy_range_string = columns_to_be_copied[column] + ‘:’ + columns_to_be_copied[column];
var paste_range_string = columns_to_be_pasted[column] + ‘:’ + columns_to_be_pasted[column];

// get source range
var source = copySheet.getRange(copy_range_string);
// get destination range
var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,1,1);

// copy values to destination range
copyTo(source,destination);
}
}

Hi David,

The issue occurred with the destination variable.

You currently have:
var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,1,1);
This gets the last active row of the destination sheet and each time, plugs in the data under the last item of Column A only. getRange(row,col,row length, col length)
To fix this you just need to change the destination Variable to:
var destination = pasteSheet.getRange(paste_range_string);

Also I noticed the copyTo was not quite right. Try this=:
source.copyTo(destination);

Here is the updated code:


function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Test");
    var pasteSheet = ss.getSheetByName("Sheet5");

var columns_to_be_copied = ['B', 'L', 'M', 'V'];
  var columns_to_be_pasted = ['A', 'B', 'C', 'D'];

for (column in columns_to_be_copied){
    var copy_range_string = columns_to_be_copied[column] + ':' + columns_to_be_copied[column];
    var paste_range_string = columns_to_be_pasted[column] + ':' + columns_to_be_pasted[column];
// get source range
var source = copySheet.getRange(copy_range_string);

// get destination range
var destination = pasteSheet.getRange(paste_range_string);

// copy values to destination range
source.copyTo(destination);

}
}

function copyInfo() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var copySheet = ss.getSheetByName("Test");

var pasteSheet = ss.getSheetByName("Sheet5");

var columns_to_be_copied = ['B', 'L', 'M', 'V'];

var columns_to_be_pasted = ['A', 'B', 'C', 'D'];

for (column in columns_to_be_copied){

var copy_range_string = columns_to_be_copied[column] + ':' + columns_to_be_copied[column];

var paste_range_string = columns_to_be_pasted[column] + ':' + columns_to_be_pasted[column];

// get source range

var source = copySheet.getRange(copy_range_string);

// get destination range

var destination = pasteSheet.getRange(paste_range_string);

// copy values to destination range

source.copyTo(destination);

}

If you are planning on adding the data to one after the lastRow of the Destination sheet I would recommend changing your get ranges to nummber values instead of A1 notation.

Conversion from A1 is a pain.

Hope this helps.

Thank you, Yagi!

So helpful!!!

I have two problems now:

1) column V contains a formula in the Test tab, so when it copies, in the tab Sheet5 shows formula and not the value (plain text);

2) I change [‘A’, ‘B’, ‘C’, ‘D’] to numbers but I don’t get what I need, it pastes all over the file. And yes, you are right, I need to keep adding below new values. Like every time when I run the script, it should add to .getLastRow()+1. I tried everything and I don’t know how to do this.;

Sorry to bother with this, but it would be helpful if you can have a look again?

I want to add after that when Column T and Column U in tab “Test” are not empty, then to copy the values from B,L,M,V:

if ((cellT$ !==””)&&(cellU$ ==””)) {
cellU.setValue(“Copied”); //mark row as “Copied”

With this I would avoid having coping duplicates. I hope this will prevent it…

So if you can just verify the two problems I sent on the top plus if with that solution this would work:

function copyInfo() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var copySheet = ss.getSheetByName(“Test”);
var pasteSheet = ss.getSheetByName(“Sheet5″);

var columns_to_be_copied = [‘B’, ‘L’, ‘M’, ‘V’];
var columns_to_be_pasted = [‘A’, ‘B’, ‘C’, ‘D’];

for (column in columns_to_be_copied){
var copy_range_string = columns_to_be_copied[column] + ‘:’ + columns_to_be_copied[column];
var paste_range_string = columns_to_be_pasted[column] + ‘:’ + columns_to_be_pasted[column];

// get source range
var source = copySheet.getRange(copy_range_string);

// get destination range
var destination = pasteSheet.getRange(paste_range_string);

var cellT = copySheet.getActiveRange();
var cellT$ = cellT.getValue();
var cellU = copySheet.getActiveRange();
var cellU$ = cellU.getValue();

if ((cellT$ !==””)&&(cellU$ ==””)) {
cellU.setValue(“Copied”); //mark row as “Copied”
// copy values to destination range
source.copyTo(destination);
}

}
}

Thanks very much for your time!!!
David

Hi David,

To only copy and paste the values, you have two options:
1) setValues : here you would have to first getValues of your source range;

destination.setValue(source.getValues());

1	destination.setValue(source.getValues());

2) copyValuesToRange; : here you add the: destination sheet, target column start, target column end, row column start, row column end. It’s a bit yuck quite frankly.

;<code>source.copyValuesToRange(destinaton, 3,3,1,1);</code></pre>

1 2	;<code>source.copyValuesToRange(destinaton, 3,3,1,1);</code></pre>

I realised that I was a little unclear on what you can keep in A1 notation and what you will need to change to number format. You can keep the A1 notation (Letters for columns) when copying data from your source but you will need to paste your data using numerical values.

Let’s say we use example 1.1 above. It might look a little like this:


    // get source range
    var source = copySheet.getRange(copy_range_string); //No change
    
    //get destination range
    var destinationLastRow = pasteSheet.getLastRow

    //Getting the last row of the destination. Column B (2) is the location and pasting the length of the row
    // equal to the length of the source data. 
    var destination = pasteSheet.getRange(destinationLastRow +1 ,2,source.length);
    destination.setValue(source.getValues());

// get source range

var source = copySheet.getRange(copy_range_string); //No change

//get destination range

var destinationLastRow = pasteSheet.getLastRow

//Getting the last row of the destination. Column B (2) is the location and pasting the length of the row

// equal to the length of the source data.

var destination = pasteSheet.getRange(destinationLastRow +1 ,2,source.length);

destination.setValue(source.getValues());

Hope this helps.

David says:

at 6:49 pm

Now it says: Cannot find method getRange(string,number,(class)).
var destination = pasteSheet.getRange(destinationLastRow +1,2,source.length);

Really don’t know what to do here 🙁

Loading...
David says:

at 7:21 pm

Here is the file example, so that you can easily understand what I need.

Thanks a million for your time with this, it is very important!
David

Loading...

Yagi says:

at 11:51 am

Hi David,

I couldn’t access your sheet. However, I wrote a tutorial last night that should answer your questions.
Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

Loading...

Reply
Henry Dishington says:

at 6:52 am

Yagi,

I tried running your code, but I keep getting an error. Let me back up and start from the beginning… I’ve got two sheets in a Google Sheets spreadsheet. I need to copy them both (including column widths) and paste as values (no formulas) with one sheet renamed to a cell value in the original sheet, then placed in (or moved to) a different folder.

Currently, I’m getting the error “A sheet with the name “Copy with format” already exists. Please enter another name. (line 8, file “Code”)

Why is this happening, and how can it be fixed?

Loading...

Reply
Rin says:

at 6:36 am

Thank you so much!
This code helped me with my project.

Loading...

Reply
1. Yagi says:
  
  at 8:59 am
  
  Great to hear, Rin!
  
  Happy coding.
  
  ~Yagi
  
  Loading...
  
  Reply

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

The Example

Like this:

Related

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

Leave a ReplyCancel reply

The Example

Share this:

Like this:

Related

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

Leave a ReplyCancel reply