0

Currently the function only allows me to get a range and append it to the "Data" sheet. But I have no way of appending that value from "Sheet3" to another column for instance "D2:D". If I try .getRange("D2") it gives me an error. I'm not sure how to incorporate it.

let currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

function testMoveValues() 
{
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
  var source = sheet.getRange("B6");
  var destSheet = currentSpreadsheet.getSheetByName("Data");
  destSheet.appendRow(source.getValues()[0]);
}
3
  • The question is unclear as the code included doesn't include .getRange("D2"). Regarding the code included, it's very likely that it throws an error because currentSpreadsheet` was not declared. By the way D2 is not a column, it's cell.
    – Rubén
    Oct 6 at 14:13
  • I didn't add the code because of the error, I see now I should add it next time. Sorry I'm new to this. Also, currentSpreadsheet is declared outside of the function. I tried the following and got an error: function testMoveValues() { let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3"); var source = sheet.getRange("B6"); var destSheet = currentSpreadsheet.getSheetByName("Data"); let range = destSheet.getRange("D2"); range.appendRow(source.getValues()[0]); }
    – Toko
    Oct 6 at 17:02
  • 1
    Please edit the question to clarify what is being asked. P.S. When including code the best is to add a "Minimal complete and verifiable example" this include the textual error message if there is any, and might also include some sample input data and the expected result.
    – Rubén
    Oct 6 at 17:07
1

To copy or move a range to another range, use Range.copyTo() or Range.moveTo() instead of Sheet.appendRow().

To append the values in a range below the last non-blank value in a column in a sheet, use the appendRows_() utility function, like this:

appendRows_(destSheet, source.getValues(), 4);

/**
* Appends an array or a value to the bottom of a range starting
* at the given column in the first row where the column is blank.
*
* @param {SpreadsheetApp.Sheet} sheet A spreadsheet sheet to append the data to.
* @param {Object[][]} data The data to append. This argument can be a 2D array, 1D array or a single value.
* @param {Number} optColumn Optional. The column number where to start appending the data. Defaults to 1.
* @return {Object} An object with fields {Range} range, {Number} rowStart, columnStart, numRows, numColumns.
*/
function appendRows_(sheet, data, optColumn) {
  // version 1.7, written by --Hyde, 11 February 2021
  if (!Array.isArray(data)) {
    data = [[data]];
  } else if (!Array.isArray(data[0])) {
    data = [data];
  }
  const rowStart = getLastRow_(sheet, optColumn) + 1;
  const columnStart = Number(optColumn) || 1;
  const numRows = data.length;
  const numColumns = data[0].length;
  const range = sheet.getRange(rowStart, columnStart, numRows, numColumns);
  range.setValues(data);
  return { range: range, rowStart: rowStart, columnStart: columnStart, numRows: numRows, numColumns: numColumns };
}

/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.5, written by --Hyde, 4 April 2021
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}
2
  • Is there a way to also control the row number you want to start at, for instance defining a variable "startingRow" and just changing its value for different rows? Even though the code above does work, I'm having a hard time understanding it. I tried changing a few values to start at row 3 instead of 2 but it skips one cell every time appendRows_() is called
    – Toko
    Oct 6 at 17:52
  • 1
    The appendRows_()function starts at the given column in the first row where the column is completely empty. If you want to start in row 3, put a value in row 2 of the column. The value can be anything, such as a single space character. Oct 6 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.