Paste Special Values

Question

I have a range consisting of 3 columns and 2 (or more) rows. The middle column contains a formula: =TRANSPOSE(SPLIT(A1,","))

The script needs to move (cut) that range onto another sheet as values, not formulas.

Does google-apps-script have a means of doing "PasteSpecial - Values"?

Here is the line I'm currently using:

sheet1.getRange("F1:H3").moveTo(sheet2.getRange("A1")); 

Can anyone tell me how I can lock those values in before they move onto sheet2 ?

(FYI: this requires a code solution only)

Answer 1

Use getValues() on the source range and setValues() on the destination. You must ensure the ranges are the same dimensions. You can then clear() the source.

Here's a utility function that does the job. It's also available as a gist. Note that it takes Range Objects as parameters.

Code

/**
 * Move all values from source range to destination range. Upon
 * completion, source range will be cleared. Source values will
 * be moved into a destination range starting at the "top left"
 * of the destination range, using the dimensions of the source
 * range. This is a blind copy, with no overwrite test.
 *
 * @param {Range} source Range Object to take values from.
 * @param {Range} destination Range Object to receive values.
 *
 * @returns nothing
 */
function moveRange(source,destination) {
  var sourceSheet = source.getSheet();
  var destSheet = destination.getSheet();
  var sourceData = source.getValues();
  var dest = destSheet.getRange(
    destination.getRow(),        // Top row of destination
    destination.getColumn(),     // left col of destination
    sourceData.length,           // # rows in source
    sourceData[0].length);       // # cols in source (elements in first row)
  dest.setValues(sourceData);
  source.clear();
}

Test Function

A successful test will clear the entire source range, and its contents will appear in the destination range as values only. The destination dimensions will match the source dimensions, regardless of what's provided as destination - it's only the top-left corner that anchors the move.

function test_moveRange() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];

  var source = sourceSheet.getRange("A7:C10");
  var destination = destSheet.getRange("C4:H2");
  moveRange(source,destination);
}

Answer 2

Just as an alternative, you can use copyTo() with advanced arguments to copy values only. To mimic the effect of moveTo(), you would still need to clear the source range.

Also, if it's easier, getRange() accepts a string reference that includes the sheet name. So:

function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange('Sheet1!F1:H3');
  source.copyTo(ss.getRange('Sheet2!A1'), {contentsOnly: true});
  source.clear();
}