2

My code currently uses the Sheet.appendRow() function to add data to a sheet. That function always writes to the first blank row starting in column A and ending in the nth column, where n is the length of the array that contains the values to be written.

How do I write to the first blank row in the sheet so starting in column D instead of column A?

Here is the code I currently have:

function testMoveValues() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet3");
  const destSheet = ss.getSheetByName("Data");
  const source = sheet.getRange("B6:G6");
  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. Commented Oct 6, 2021 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
    Commented Oct 6, 2021 at 17:02
  • 2
    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. Commented Oct 6, 2021 at 17:07

1 Answer 1

2

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

function test() {
  const ss = SpreadsheetApp.getActive();
  const sourceRange = ss.getRange('Sheet3!B6:G6');
  const targetSheet = ss.getSheetByName('Data');
  appendRows_(targetSheet, sourceRange.getValues(), 4); // column D
}

For that to work, you will need to paste the appendRows_() and getLastRow_() utility functions in your script project.

/**
* Appends an array or a value to the bottom of a sheet starting in
* the given column after the last row where the column is blank.
*
* When optColumn is provided, appends data starting in that column
* after the last row where that column has visible content.
*
* When optColumn is not provided, appends data starting in column A
* after the last row that has visible content in any column.
*
* Use Range.getRichTextValues() to preserve text formatting such as bold,
* italic, underlined, font and text colors when appending textual data.
* To append numbers, dates and plain text, use Range.getValues().
*
* @param {SpreadsheetApp.Sheet} sheet A spreadsheet sheet to append the data to.
* @param {Object[][]} data The plain or formatted data to append: a 2D array, a 1D array or a single value.
* @param {SpreadsheetApp.Range} optColumn Optional. The 1-indexed position of a column in the sheet, or a SpreadsheetApp.Range object that covers the columns where to find the last row with visible content, or an A1 type text string such as 'A:A' or 'A:B'.
* @return {Object} An object with fields {Range} range, {Number} rowStart, columnStart, numRows, numColumns.
*/
function appendRows_(sheet, data, optColumn) {
  'use strict';
  // version 1.14, written by --Hyde, 14 May 2024
  //  - see https://webapps.stackexchange.com/a/159426/269219
  const lock = LockService.getDocumentLock();
  lock.waitLock(30 * 1000);
  data = Array.isArray(data) ? (Array.isArray(data[0]) ? data : [data]) : [[data]];
  let action = 'setValues';
  if (data.some(row => row.some(value => value && value.getRuns))) {
    action = 'setRichTextValues';
    data = data.map(row => row.map(value => value && value.getRuns
      ? value
      : SpreadsheetApp.newRichTextValue().setText(value).build()
    ));
  }
  const rowStart = getLastRow_(sheet, optColumn) + 1;
  const columnStart = optColumn?.getColumn?.() || Number(optColumn) || 1;
  const numRows = data.length;
  const numColumns = data[0].length;
  const range = sheet.getRange(rowStart, columnStart, numRows, numColumns);
  range[action](data);
  lock.releaseLock();
  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 {SpreadsheetApp.Sheet} sheet Optional. A sheet in a spreadsheet. Defaults to the active sheet.
* @param {SpreadsheetApp.Range} column Optional. The 1-indexed position of a column in the sheet, or a SpreadsheetApp.Range object that covers the columns where to find the last row with visible content, or an A1 type text string such as 'A:A' or 'A:B'.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet = SpreadsheetApp.getActiveSheet(), column) {
  // version 1.6, written by --Hyde, 18 March 2023
  const values = (
    typeof column === 'number'
      ? sheet.getRange(1, column, sheet.getLastRow() || 1, 1)
      : typeof column === 'string'
        ? sheet.getRange(column)
        : column
          ? sheet.getRange(1, column.getColumn(), sheet.getLastRow(), column.getWidth())
          : 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
    Commented Oct 6, 2021 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. Commented Oct 6, 2021 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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