Copy and paste with .getValues / .setValues, but clearing the array inbetween

Question

I have a table which looks like this:

I want to copy the first two columns of data and insert them before the EOF, but empty them before the paste. The end result should look like this:

So far, my code looks like this:

function insertCols() { //Insert new column var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var rangeToCopy = sheet.getRange(1,1,20,2).getValues(); rangeToCopy[0][0] = ""; rangeToCopy.fill(null,2); var copyDest = sheet.getRange(1,11,20,2); copyDest.activate(); copyDest.insertCells(SpreadsheetApp.Dimension.COLUMNS); copyDest.setValues(rangeToCopy); }

The .setValues line throws the error:

Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 2.

What's going on, and how can I alter my code to get the desired result?

doubleunary · Accepted Answer · 2025-11-21 22:33:39Z

You don't need to copy the all the rows in the range if you just want to add columns and copy the headers. When you insert columns, they inherit formatting from the previous column automatically. Use Sheet.insertColumnsAfter(), like this:

function insertCols() { const sheet = SpreadsheetApp.getActiveSheet(); const columnStart = sheet.getLastColumn() - 1; sheet.insertColumnsAfter(columnStart, 2); sheet.getRange('A2:B2').copyTo(sheet.getRange(2, columnStart + 1)); }

See Sheet.insertColumnsAfter().

Collectives™ on Stack Overflow

Copy and paste with .getValues / .setValues, but clearing the array inbetween

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related