CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dark Kitsune
Dark Kitsune

Reputation: 7

spreadsheets script: skip rows with an empty first column

I wrote a script for the google spreadsheet to save the logs of a single sheet. but this script writes the date in the first column, including for empty cells. For example, if there are 1000 rows on the test1 sheet, of which only 50 are filled, the script will still bring 1000 rows to the tech.logs tab. I want it to process only those rows from the test1 sheet in which the first column is not empty. I tried to implement this via if (! cell.isBlank ()) but I get the error "This is not a function". How can I do this?

function recordHistory() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("test1");
var source = inputSheet.getRange("A2:AX");
var values = source.getValues();
for (var i=0; i<values.length; i++) values[i][0] = new Date();
var outputSheet = ss.getSheetByName("tech.logs");
outputSheet.getRange(outputSheet.getLastRow()+1,1,values.length,values[0].length).setValues(values);
var range = outputSheet.getRange("A1:A");
range.sort(1);
};

Upvotes: 0

Views: 1009

Answers (2)

doubleunary
doubleunary

Reputation: 18708

Use Array.filter() to weed out rows where the first value in a row is blank, like this:

function recordHistory() {
  const now = new Date();
  const ss = SpreadsheetApp.getActive();
  const targetSheet = ss.getSheetByName('tech.logs');
  const values = ss.getRange('test1!A2:AX').getValues()
    .filter(row => String(row[0]))
    .forEach(row => row[0] = now);
  const range = appendRows_(targetSheet, values).range;
}

appendRows_() is a utility function that handles the details of appending a 2D array to the end of a sheet.

/**
* 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, or the position of the last row that
* has visible content in any column of the sheet.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet. When missing, the function returns the position of the last row that has visible content in any column.
* @return {Number} The 1-indexed row number of the last row that has visible content in column columnNumber, or 0 if the column is blanky.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.4, written by --Hyde, 11 February 2021
  const values
    = columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1).getDisplayValues()
      : sheet.getDataRange().getDisplayValues();
  for (var row = values.length - 1; row >= 0; row--) {
    if (values[row].join('')) {
      break;
    }
  }
  return row + 1;
}

Upvotes: 0

idfurw
idfurw

Reputation: 5852

It should be range.isBlank() but you are recommended to getValues() in batch are compare each cell in a loop.

You may also consider to use getDataRange() to avoid getting empty range that is not needed

var source = inputSheet.getDataRange();
// var source = inputSheet.getRange("A2:AX");
var values = source.getValues();
values.splice(0, 1);
for (var i=0; i<values.length; i++) {
  if (values[i][0] !== '') { values[i][0] = new Date(); }
}

Upvotes: 1

Related Questions