Google Sheets - Time Stamps and clearContent

Question

I'm trying to create a google sheets system for people to use to help keep track of their daily activities. We are currently hand writing this out every day. I orignally created a similar one on excel but I need it to be usable on multiple systems by muiltiple people so I'm moving it google sheets.

What I currently cannot figureout is how to properly use clearContents on a row when the information in column 1 is deleted (between A5 and A55).

In short, when cell A6 is deleted I want to use clearConents on the rest of row 6 but when data is entered in A6 it populates the time into different cells on row 6.

function onEdit(e) {
  var sheet      = e.source.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var col        = activeCell.getColumn();
  var row        = activeCell.getRow();
  
  
  //Start Time Function
  if (col == 1 && isBlank){   // These first two rows are what I cannot figure out.
    sheet.getRange(row).clearContent();
  }
   else if (col == 1){
    sheet.getRange(row, col+11).setValue(new Date()).setNumberFormat('hh:mm:ss');
    sheet.getRange(row,col+13).setValue(new Date()).setNumberFormat('hhmm');
    sheet.getRange(row, col+5).setValue(new Date()).setNumberFormat('hhmm');
    sheet.getRange(row, col+3).setValue(new Date()).setNumberFormat();
   }
  
  
  //End Time Function
  if (col == 8) {
    sheet.getRange(row, col+5).setValue(new Date()).setNumberFormat('hh:mm:ss');
    sheet.getRange(row, col+7).setValue(new Date()).setNumberFormat('hhmm');
    sheet.getRange(row, col-1).setValue(new Date()).setNumberFormat('hhmm');
  }
} 

Answer 1

Modifications

Based on the other answer, your working code can be optimized with the following modifications:

1. onEdit trigger event object has a range property that you can use instead of the getActiveCell (regardless, it is recommended to use getCurrentCell instead).
2. Use batch methods (like setValues, setNumberFormats) and make modifications on in-memory objects. Calling I/O (input/output) methods (like getRange or setValue in a loop or repeatedly) is slow.
3. Use type-safe comparison with ===. Why? Take a look at this:

console.log( 0 == "" );
 console.log( [] == "" );
 
 console.log( 0 === "" );
 console.log( [] === "" );

With all modifications applied, your script becomes the following:

function onEdit({
  source,
  range: currRng
}) {

  var sheet = source.getActiveSheet();

  var col = currRng.getColumn();
  var row = currRng.getRow();
  var val = currRng.getValue();

  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();

  const range = sheet.getRange(1, 1, maxRows, maxCols);

  const values = range.getValues();

  const formats = range.getNumberFormats();

  const now = new Date();
  const hhmmssFormat = "hh:mm:ss";
  const hhmmFormat = "hhmm";

  const colIdx = col - 1;
  const rowIdx = row - 1;

  //Start Time Function
  if (col === 1 && row >= 5 && row <= 55 && val === "") {
    sheet.getRange(row, 1, 1, maxCols).clearContent();
  } else if (col === 1) {

    const newValues = values.map((row, ri) => {
      if (ri === rowIdx) {
        row[colIdx + 11] = now;
        row[colIdx + 13] = now;
        row[colIdx + 5] = now;
        row[colIdx + 3] = now;
      }
      return row;
    });

    const newFormats = formats.map((row, ri) => {
      if (ri === rowIdx) {
        row[colIdx + 11] = hhmmssFormat;
        row[colIdx + 13] = hhmmFormat;
        row[colIdx + 5] = hhmmFormat;
        row[colIdx + 3] = hhmmFormat;
      }
      return row;
    });

    range.setValues(newValues);
    range.setNumberFormats(newFormats);

    console.log(range.getValues()[14]);
    console.log(range.getNumberFormats()[14]);
  }

  //End Time Function
  if (col === 8) {

    const newValues = values.map(row => {
      row[colIdx + 5] = now;
      row[colIdx + 7] = now;
      row[colIdx - 1] = now;
      return row;
    });

    const newFormats = formats.map(row => {
      row[colIdx + 5] = hhmmssFormat;
      row[colIdx + 7] = hhmmFormat;
      row[colIdx - 1] = hhmmFormat;
      return row;
    });

    range.setValues(newValues);
    range.setNumberFormats(newFormats);
  }
}

---

You must have V8 runtime enabled for the above to work.

---

Working snippet using mocks:

function Range({
  sheet,
  grid,
  row = 1,
  column = 1,
  numRows = 1,
  numColumns = 1
}) {

  const intRow = row - 1;
  const intCol = column - 1;

  /** @type {function (any[][], function)} */
  const sliceMap = (g, row, col, rows, cols, mapper) => g.slice(row, row + rows).map(
    (r) => r.slice(intCol, col + cols).map(mapper)
  );

  /** @type {function (any[][], any[][], number, number, string)} */
  const sliceUpdate = (g, ug, row, col, prop) => g.forEach(
    (r, ri) => r.forEach((c, ci) => {
      if (ri < row || ci < col) {
        return;
      }

      const arow = ri - row;
      const acol = ci - col;

      g[ri][ci][prop] = ug[arow][acol];
    })
  );

  return {
    activate() {
      sheet.setActiveRange(this);
      return this;
    },
    clearContent() {
      grid.forEach((r, ri) => r.forEach((c, ci) => {
        ri >= intRow && ci >= intCol && (grid[ri][ci].value = "");
      }));
      return this;
    },
    getCell(row, column) {
      return Range({
        sheet,
        grid,
        row: row + intRow,
        column: column + intCol
      });
    },
    getColumn() {
      return column;
    },
    getFormulas() {
      return sliceMap(grid, numRows, numColumns, ({
        formula
      }) => formula);
    },
    setFormulas(formulas) {
      sliceUpdate(grid, formulas, intRow, intCol, "formula");
      return this;
    },
    getNumberFormats() {
      return sliceMap(grid, intRow, intCol, numRows, numColumns, ({
        format
      }) => format);
    },
    setNumberFormats(numberFormats) {
      sliceUpdate(grid, numberFormats, intRow, intCol, "format");
      return this;
    },
    getRow() {
      return row;
    },
    getValue() {
      const [val] = sliceMap(grid, intRow, intCol, numRows, numColumns, ({
        value
      }) => value);
      return val[0];
    },
    getValues() {
      return sliceMap(grid, intRow, intCol, numRows, numColumns, ({
        value
      }) => value);
    },
    setValues(values) {
      sliceUpdate(grid, values, intRow, intCol, "value");
      return this;
    }
  };
}

function Sheet(spreadsheet) {

  /** @type {{ value }[][]} */
  const grid = [];

  let sheetName = "Sheet1";

  let active = null;

  return {
    activate() {
      spreadsheet.setActiveSheet(this);
      return this;
    },
    getCurrentCell() {
      return active && active.getCell(
        active.getRow(),
        active.getColumn()
      );
    },
    getDataRange() {
      return Range({
        grid,
        sheet: this
      });
    },
    getLastRow() {
      return grid.findIndex(row => row.some(({
        value
      }) => value !== "")) + 1 || 1;
    },
    getLastColumn() {
      const lasts = grid.map(row => row.reverse().findIndex(({
        value
      }) => value !== "") + 1);
      return Math.max(...lasts) || 1;
    },
    getMaxColumns() {
      return grid[0].length;
    },
    getMaxRows() {
      return grid.length;
    },
    getRange(row, column, numRows = 1, numColumns = 1) {
      return Range({
        grid,
        sheet: this,
        row,
        column,
        numRows,
        numColumns
      });
    },
    getSheetName() {
      return sheetName;
    },
    insertColumns(columnIndex, numColumns) {
      grid.forEach(row => {
        const cols = new Array(numColumns).fill("")
          .map(() => ({
            value: "",
            formula: "",
            format: ""
          }));
        row.splice(columnIndex, 0, ...cols);
      });
      return this;
    },
    insertRows(rowIndex, numRows) {
      const rows = new Array(numRows).fill("")
        .map(() => [{
          value: "",
          formula: "",
          format: ""
        }]);
      grid.splice(rowIndex, 0, ...rows);
      return this;
    },
    setActiveRange(range) {
      active = range;
      return this;
    },
    setName(name) {
      sheetName = name;
      return this;
    }
  };
}

function Spreadsheet() {

  const defaultSheet = Sheet(this);

  const sheets = [defaultSheet];

  let active = defaultSheet;

  return {
    getSheets() {
      return sheets;
    },
    getActiveSheet() {
      return active;
    },
    insertSheet(sheetIndex = 0) {
      const sheet = Sheet(this);
      sheets.splice(sheetIndex, 0, sheet);
      return sheet.activate();
    },
    setActiveSheet(sheet) {
      active = sheet;
      return sheet;
    }
  };
}

var SpreadsheetApp = {
  getActiveSpreadsheet() {
    return Spreadsheet();
  }
};

function onEdit({
  source,
  range: currRng
}) {

  var sheet = source.getActiveSheet();

  var col = currRng.getColumn();
  var row = currRng.getRow();
  var val = currRng.getValue();

  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();

  const range = sheet.getRange(1, 1, maxRows, maxCols);

  const values = range.getValues();

  const formats = range.getNumberFormats();

  const now = new Date();
  const hhmmssFormat = "hh:mm:ss";
  const hhmmFormat = "hhmm";

  const colIdx = col - 1;
  const rowIdx = row - 1;

  //Start Time Function
  if (col === 1 && row >= 5 && row <= 55 && val === "") {
    sheet.getRange(row, 1, 1, maxCols).clearContent();
  } else if (col === 1) {

    const newValues = values.map((row, ri) => {
      if (ri === rowIdx) {
        row[colIdx + 11] = now;
        row[colIdx + 13] = now;
        row[colIdx + 5] = now;
        row[colIdx + 3] = now;
      }
      return row;
    });

    const newFormats = formats.map((row, ri) => {
      if (ri === rowIdx) {
        row[colIdx + 11] = hhmmssFormat;
        row[colIdx + 13] = hhmmFormat;
        row[colIdx + 5] = hhmmFormat;
        row[colIdx + 3] = hhmmFormat;
      }
      return row;
    });

    range.setValues(newValues);
    range.setNumberFormats(newFormats);

    console.log(range.getValues()[14]);
    console.log(range.getNumberFormats()[14]);
  }

  //End Time Function
  if (col === 8) {

    const newValues = values.map(row => {
      row[colIdx + 5] = now;
      row[colIdx + 7] = now;
      row[colIdx - 1] = now;
      return row;
    });

    const newFormats = formats.map(row => {
      row[colIdx + 5] = hhmmssFormat;
      row[colIdx + 7] = hhmmFormat;
      row[colIdx - 1] = hhmmFormat;
      return row;
    });

    range.setValues(newValues);
    range.setNumberFormats(newFormats);
  }
}

//Test:
(() => {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();
  sh.insertRows(0, 55);
  sh.insertColumns(0, 13);
  const rng = sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns());

  rng.activate();

  const vals = rng.getValues();
  vals[14][0] = "val";
  rng.setValues(vals);

  onEdit({
    source: ss,
    range: sh.getRange(15, 1)
  });
})();

Answer 2

Explanation:

You are very close. Essentially you need to modify the first if condition like that:

if (col == 1 && row>=5 && row<=55 && sheet.getRange(row,col).getValue() == "")

• col == 1: cell in column A has to be edited
• row >=5 and <=55: cell between A5 and A55 has to be edited.
• value == "": you cleared its value.

and clear the full row if the aforementioned condition evaluates to true:

sheet.getRange(row,1,1,sheet.getMaxColumns()).clearContent();

---

Solution:

function onEdit(e) {
  var sheet      = e.source.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var col        = activeCell.getColumn();
  var row        = activeCell.getRow();
  
  //Start Time Function
  if (col == 1 && row>=5 && row<=55 && sheet.getRange(row,col).getValue() == ""){ 
    sheet.getRange(row,1,1,sheet.getMaxColumns()).clearContent();
  }
    else if (col == 1){
    sheet.getRange(row, col+11).setValue(new Date()).setNumberFormat('hh:mm:ss');
    sheet.getRange(row,col+13).setValue(new Date()).setNumberFormat('hhmm');
    sheet.getRange(row, col+5).setValue(new Date()).setNumberFormat('hhmm');
    sheet.getRange(row, col+3).setValue(new Date()).setNumberFormat('hhmm');
   }
  
  //End Time Function
  if (col == 8) {
    sheet.getRange(row, col+5).setValue(new Date()).setNumberFormat('hh:mm:ss');
    sheet.getRange(row, col+7).setValue(new Date()).setNumberFormat('hhmm');
    sheet.getRange(row, col-1).setValue(new Date()).setNumberFormat('hhmm');
  }
} 

---

References:

• getMaxColumns()
• getRange(row, column, numRows, numColumns)