spreadsheets script: skip rows with an empty first column

Question

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);
};

idfurw · Accepted Answer · 2021-08-08 10:42:30Z

1

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(); }
}

edited Aug 8, 2021 at 10:42

answered Aug 8, 2021 at 9:58

idfurw

5,8482 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dark Kitsune Over a year ago

thank you!! But now I have lost the date from the first column and I also get an error( TypeError: Cannot read property '0' of undefined

idfurw Over a year ago

Sorry. I thought you want to check for first row. I have corrected it.

doubleunary · Accepted Answer · 2021-08-08 11:05:01Z

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;
}

Collectives™ on Stack Overflow

spreadsheets script: skip rows with an empty first column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related