Benchmark: Process Costs for Retrieving 1st Empty Cell and 1st Non Empty Cell of Specific Column in Google Spreadsheet using Google Apps Script

report / GasTips / Benchmark / spreadsheet
Google Apps Script / spreadsheet

Gists

Introduction

Here, I would like to report the process costs for retrieving the 1st empty cell or 1st non empty cell of the specific column of Google Spreadsheet using Google Apps Script (GAS). For this situations, the following 2 patterns can be considered.

  1. Retrieving 1st empty cell of specific column by searching from TOP of sheet

  2. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet

Actually, when I create the applications using GAS, there is the case that it’s required to retrieve the 1st empty cell or 1st non empty cell of the specific column. But, in such case, I would like to make the process cost of this as low as possible comparing with other part of the application. From such my experience, I thought that when the process cost of this can be reduced, it will be also useful for other users. So in this report, I would like to introduce the process cost of this situation. And, the following results were obtained.

  • In order to retrieve the 1st empty cell of the specific column by searching from TOP of sheet, the process cost of method using getNextDataCell is the lowest of all methods.

  • In order to retrieve 1st NON empty cell of specific column by searching from BOTTOM of sheet, the process cost of method using TextFinder is the lowest of all methods.

Experimental procedure

At first, I would like to mention about “Retrieving 1st empty cell of specific column by searching from TOP of sheet” and “Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet”. You can see a simple sample Spreadsheet for explaining them at Fig.1.

Fig 1. Simple sample Spreadsheet for explaining about “Retrieving 1st empty or non empty cell of specific column”.

From the Fig. 1, for the column “C”,

  • When 1st empty cell is retrieved by searching from TOP of sheet, the cell “C6” should be obtained.

  • When 1st NON empty cell is retrieved by searching from BOTTOM of sheet, the cell “C9” should be obtained.

All scripts using in this report are filled this condition.

In this report, in order to measure the process cost, the following sample Spreadsheet was used.

The sample Spreadsheet has one sheet. The sheet has 5 columns and 200,000 rows. The columns “A”, “B” and “D” are no cell values for all rows. The column “C” has the cell value of “sample” in the rows from 1 to 100,000, and no cell values in the rows from 100,001 to 200,000. All rows from 1 to 200,000 of the column “E” have the cell value of “sample”.

In order to measure the process cost, the column “C” was used. Under this sample Spreadsheet, when the 1st empty cell of the column “C” by searching from TOP of sheet is retrieved, C100001 should be obtained. Also, when the 1st NON empty cell of the column “C” by searching from BOTTOM of sheet is retrieved, C100000 should be obtained.

Here, it thinks of the situation retrieving the 1st empty cell of the specific column. In this case, it is considered the following 2 patterns.

  1. Retrieve all cell values from the column and check the 1st empty cell from TOP of sheet and 1st non empty cell from BOTTOM of sheet. In this case, the process for searching the cell is run at the client side of Google Apps Script.

    • In this report, 2 methods of the for loop and the while loop were used.

  2. Give the range of column to the built-in method and directly retrieve the required range from the method. In this case, the process for searching the cell is run at the internal server side.

    • In this report, 2 methods of getNextDataCell and TextFinder were used.

    • From my previous reports (Ref1, Ref2, Ref3), it has already been found that when the process is run at the internal server side, the process cost becomes low.

At the following figures of result data for above 4 methods in the section of “Results and discussions”, 4 labels of “for loop”, “while”, “getNextDataCell” and “TextFinder” are used. And, you can see these sample script for measuring the process cost at Appendix.

By the way, at GAS, the processing time is not stable as you know. So the average value for more than 200 times measurements was used for each data point which is shown by figures. At this time, the fluctuation of the average values was less than 1 %. I worry that each detailed-data point at my environment might be different from that at other user’s environment. But I think that the trend of this result can be used.

Results and discussions

1. Retrieving 1st empty cell of specific column by searching from TOP of sheet

Fig 2. Process time vs 4 methods for retrieving 1st empty cell of specific column by searching from TOP of sheet.

From Fig. 2, it was found that the ascending order of the process costs of these methods was “getNextDataCell”, “TextFinder”, “while” and “for loop”. When the process speed (v axis) of Fig. 2 is seen, the speed of “getNextDataCell” is 1.7 times faster than “TextFinder”, 12.9 times faster than “while” and 23.3 times faster than “for loop”. The process costs of “getNextDataCell” and “TextFinder” are much lower than those of “for loop” and “while”. This result is as same as expected from these references. (Ref1, Ref2, Ref3)

2. Retrieving 1st non empty cell of specific column by searching from BOTTOM of sheet

Fig 3. Process time vs 4 methods for retrieving 1st non empty cell of specific column by searching from BOTTOM of sheet.

From Fig. 3, it was found that the ascending order of the process cost of these methods was “TextFinder”, “while”, “for loop” and “getNextDataCell”. When the process speed (v axis) of Fig. 3 is seen, the speed of “TextFinder” is 10.0 times faster than “while”, 10.3 times faster than “for loop” and 17.5 times faster than “getNextDataCell”. In this case, it was found that the process cost of the method using “TextFinder” is lowest of all, and, the process cost of “getNextDataCell” is highest of all. This is the interesting result. At “getNextDataCell”, when all cell values of the column are empty and no empty, the 1st empty cell cannot be directly retrieved using SpreadsheetApp.Direction.DOWN and SpreadsheetApp.Direction.UP. By this, it is required to add more script for retrieving the 1st empty and the 1st non empty cell. It is considered that the reason of this high cost is due to this additional script. When the scripts for 1st empty cell and 1st non empty cell of specific column by searching from TOP (patternA3_) and BOTTOM (patternB3_) of sheet, respectively are seen, it can understand the differences of process costs for “getNextDataCell” shown in Fig. 2 and 3. From this result, it was found that although the process cost of “getNextDataCell” is low, when “getNextDataCell” is used for retrieving the 1st non empty cell from the bottom of sheet, it is required to be careful. And, it was found that for “for loop” and “while”, the process cost is almost the same.

By the way, I also measured the method for retrieving the 1st empty cell using isBlank() of Class Range. But, in this case, when the script is run for above sample Spreadsheet, an error of Exceeded maximum execution time occurred. By this, it is not included in this result.

Summary

In this report, the process costs for retrieving the 1st empty cell and 1st non empty cell of the specific column on Google Spreadsheet using Google Apps Script have been investigated. As the result, the following results could be obtained.

  1. In order to retrieve the 1st empty cell of the specific column by searching from TOP of sheet, “getNextDataCell” is suitable.

  2. In order to retrieve the 1st empty cell of the specific column by searching from BOTTOM of sheet, “TextFinder” is suitable.

  3. From above result, when the 1st empty cell of the specific column on Google Spreadsheet using Google Apps Script is retrieved, the methods which are running at the internal server side are suitable.

Sample scripts obtained from this report

From this report, I could obtain the following 2 sample scripts for retrieving the 1st empty cell and the 1st non empty cell of the column.

1. Retrieving 1st empty row of specific column by searching from TOP of sheet

Object.prototype.get1stEmptyRowFromTop = function (
  columnNumber,
  offsetRow = 1
) {
  const range = this.getRange(offsetRow, columnNumber, 2);
  const values = range.getDisplayValues();
  if (values[0][0] && values[1][0]) {
    return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  } else if (values[0][0] && !values[1][0]) {
    return offsetRow + 1;
  }
  return offsetRow;
};

// Please run this function.
function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const res = sheet.get1stEmptyRowFromTop(3);
  console.log(res); // Retrieve the 1st empty row of column "C" by searching from TOP of sheet.
}
  • Please give the sheet object and column number.
  • In this script, when 2nd argument is used, you can set the offset row. For example, when the 1st and 2nd rows are the header rows, you can use this script as const res = sheet.get1stEmptyRowFromTop(3, 2);.

2. Retrieving 1st NON empty row of specific column by searching from BOTTOM of sheet

Object.prototype.get1stNonEmptyRowFromBottom = function (
  columnNumber,
  offsetRow = 1
) {
  const search = this.getRange(offsetRow, columnNumber, this.getMaxRows())
    .createTextFinder(".")
    .useRegularExpression(true)
    .findPrevious();
  return search ? search.getRow() : offsetRow;
};

// Please run this function.
function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const res = sheet.get1stNonEmptyRowFromBottom(3);
  console.log(res); // Retrieve the 1st non empty row of column "C" by searching from BOTTOM of sheet.
}
  • Please give the sheet object and column number.
  • In this script, when 2nd argument is used, you can set the offset row. For example, when the 1st and 2nd rows are the header rows, you can use this script as const res = sheet.get1stNonEmptyRowFromBottom(3, 2);.

Appendix

Scripts

Retrieving 1st empty cell of specific column by searching from TOP of sheet

// Pattern 1: for loop
const patternA1_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const values = sheet
    .getRange(1, columnNumber, sheet.getLastRow())
    .getDisplayValues();
  let row = 0;
  for (row = 0; row < values.length; row++) {
    if (!values[row][0]) break;
  }
  return row + 1;
};

// Pattern 2: while
// Ref: https://stackoverflow.com/a/9102463
//
// In this script, when all cells of the column have the values, an error "Cannot read property '0' of undefined" occurred. So I modified the condition of while.
const patternA2_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const values = sheet
    .getRange(1, columnNumber, sheet.getLastRow())
    .getDisplayValues();
  const len = values.length;
  let row = 0;
  while (row < len && values[row][0]) row++;
  return row + 1;
};

// Pattern 3: getNextDataCell
const patternA3_ = (columnNumber) => {
  const range = SpreadsheetApp.openById(ssId)
    .getSheets()[0]
    .getRange(1, columnNumber);
  return range.getDisplayValue()
    ? range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1
    : 1;
};

// Pattern 4: TextFinder
const patternA4_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const search = sheet
    .getRange(1, columnNumber, sheet.getLastRow())
    .createTextFinder("^$")
    .useRegularExpression(true)
    .matchEntireCell(true)
    .findNext();
  return search ? search.getRow() : sheet.getLastRow() + 1;
};

Retrieving 1st empty cell of specific column by searching from BUTTOM of sheet

// Pattern 1: for loop
const patternB1_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const values = sheet
    .getRange(1, columnNumber, sheet.getLastRow())
    .getDisplayValues();
  let row;
  for (row = values.length - 1; row >= 0; row--) {
    if (values[row][0]) break;
  }
  return row + 1;
};

// Pattern 2: while
const patternB2_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const values = sheet
    .getRange(1, columnNumber, sheet.getLastRow())
    .getDisplayValues();
  let row = values.length - 1;
  while (row >= 0 && !values[row][0]) row--;
  return row + 1;
};

// Pattern 3: getNextDataCell
const patternB3_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const maxRow = sheet.getMaxRows();
  const range = sheet.getRange(1, columnNumber, maxRow);
  const v = range.getDisplayValues();
  if (v[v.length - 1][0]) {
    return maxRow;
  } else if (!v[0][0]) {
    return 1;
  } else {
    return range
      .offset(maxRow - 1, 0)
      .getNextDataCell(SpreadsheetApp.Direction.UP)
      .getRow();
  }
};

// Pattern 4: TextFinder
const patternB4_ = (columnNumber) => {
  const sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  const search = sheet
    .getRange(1, columnNumber, sheet.getMaxRows())
    .createTextFinder(".")
    .useRegularExpression(true)
    .findPrevious();
  return search ? search.getRow() : 1;
};

 Share!