Google Sheets

Google Apps Script

https://developers.google.com/apps-script

Automations

Auto-sort by column

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()); // Get data starting from row 2 (Assumes row 1 is a header row )
  
      if (sheet.getName() === "Sheet1") {
    // Sort the range by column 6 (F), ascending
    range.sort({ column: 6, ascending: true });
  }
}

Handle Manual Execution

• If you want to run the script manually for testing, you can modify the code to handle the absence of the event object (e).

 if (!e) {
    // Simulate the event object for manual testing
    e = { source: SpreadsheetApp.getActiveSpreadsheet() };
  }
 
  const sheet = e.source.getActiveSheet();

Create dynamic header dictionary

// Function to create the header dictionary
function getHeaderDict(sheet) {
  const lastRow = sheet.getLastRow();
  const headerRow = 1; // The row containing the headers
 
  // Get the headers from the first row
  const headers = sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0];
 
  // Create a dictionary (object) of headers with their column indexes
  const headerDict = headers.reduce((acc, header, index) => {
    acc[header] = index + 1; // Add 1 because column indexes are 1-based in Apps Script
    return acc;
  }, {});
 
  Logger.log('Header Dictionary: %s', JSON.stringify(headerDict));
  return headerDict;
}

Auto-sort with dynamic header lookup

function sortData(sheet, headerDict){
  const lastRow = sheet.getLastRow();
  const dateColumnIndex = headerDict["c"];
 
  if (!dateColumnIndex) {
    console.error("The 'Date' column was not found.");
    return;
  }
 
  // Define the data range starting from row 2
  const dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
 
  // Sort the range by the "Date" column, ascending
  dataRange.sort({ column: dateColumnIndex, ascending: true });
}

Add timestamp

// Auto-add timestamp (or clear if rest of row was cleared)
function addTimestamp(sheet, row, headerDict) {
  const dateAddedIndex = headerDict["Date Added"];
  const lastCol = sheet.getLastColumn();
 
  const rowValues = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
  const dateValue = rowValues[dateAddedIndex - 1];
 
  // Check whether rest of row was cleared
  const hasOtherData = rowValues
    .filter((_, index) => index !== (dateAddedIndex - 1))
    .some(cell => cell !== "" && cell !== null);
 
  if (hasOtherData && !dateValue) {
    // Add timestamp if there's other data but no timestamp yet
    const timestamp = new Date();
    sheet.getRange(row, dateAddedIndex).setValue(timestamp);
  } else if (!hasOtherData && dateValue) {
    // Clear timestamp if it's the only value in the row
    sheet.getRange(row, dateAddedIndex).clearContent();
  }
}
 

Run script when sheet is edited

// Function triggered on edit event
function onEdit(e) {
  if (!e) {
    // Simulate the event object for manual testing
    e = { source: SpreadsheetApp.getActiveSpreadsheet() };
  }
 
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sheet1") return; // Only run on "Sheet1"
 
  // Get the header dictionary from the sheet
  const headerDict = getHeaderDict(sheet);
 
  const editedRange = e.range;
  const editedRow = editedRange.getRow();
 
  // Add timestamp if needed
  addTimestamp(sheet, editedRow, headerDict);
 
  // Auto-sort 
  sortData(sheet,headerDict);
}

Starter Template

Deprecated

// Function triggered on edit event
function onEdit(e) {
  if (!e) {
    // Simulate the event object for manual testing
    e = { source: SpreadsheetApp.getActiveSpreadsheet() };
  }
 
  const sheet = e.source.getActiveSheet();
  // if (sheet.getName() !== "Sheet1") return; // Only run on "Sheet1"
  const sheetName = sheet.getName(); // Get the name of the active sheet
  const validSheets = ["Sheet1", "Sheet2"]; // Array of valid sheet names
  if (!validSheets.includes(sheetName)) return; // If not in the list, exit function
  Logger.log(sheet);
 
  // Get the header dictionary from the sheet
  const headerDict = getHeaderDict(sheet);
 
  const editedRange = e.range;
  const editedRow = editedRange.getRow();
 
  // Uncomment to insert timestamp 
  // addTimestamp(sheet, editedRow, headerDict);
 
  // Auto-sort 
  sortData(sheet,headerDict);
}
 
// Function to create the header dictionary
function getHeaderDict(sheet) {
  const lastRow = sheet.getLastRow();
  const headerRow = 1; // The row containing the headers
 
  // Get the headers from the first row
  const headers = sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0];
 
  // Create a dictionary (object) of headers with their column indexes
  const headerDict = headers.reduce((acc, header, index) => {
    acc[header] = index + 1; // Add 1 because column indexes are 1-based in Apps Script
    return acc;
  }, {});
 
 
  // Convert dictionary to a formatted JSON string for better readability
  const headerDictString = JSON.stringify(headerDict, null, 2);
  Logger.log('Header Dictionary: %s', headerDictString);
  // SpreadsheetApp.getUi().alert("Header Dictionary:\n" + headerDictString);
 
  return headerDict;
}
 
function sortData(sheet, headerDict) {
  const lastRow = sheet.getLastRow();
  const dateColumn = "Date";
  const timeColumn = "Time";
 
  const dateColumnIndex = headerDict[dateColumn];
  const timeColumnIndex = headerDict[timeColumn];
 
  if (!dateColumnIndex || !timeColumnIndex) {
    console.error("The 'Date' or 'Time' column was not found.");
    SpreadsheetApp.getUi().alert("The 'Date' or 'Time' column was not found.");
    return;
  }
 
  // Define the data range starting from row 2
  const dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
 
  // Sort the range first by Date, then by Time (both ascending)
  dataRange.sort([
    { column: dateColumnIndex, ascending: true },
    { column: timeColumnIndex, ascending: true }
  ]);
}
// Function to add timestamp when a new row is added
// Auto-add timestamp (or clear if rest of row was cleared)
function addTimestamp(sheet, row, headerDict) {
  const dateAddedIndex = headerDict["Date Added"];
  const lastCol = sheet.getLastColumn();
 
  const rowValues = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
  const dateValue = rowValues[dateAddedIndex - 1];
 
  // Check whether rest of row was cleared
  const hasOtherData = rowValues
    .filter((_, index) => index !== (dateAddedIndex - 1))
    .some(cell => cell !== "" && cell !== null);
 
  if (hasOtherData && !dateValue) {
    // Add timestamp if there's other data but no timestamp yet
    const timestamp = new Date();
    sheet.getRange(row, dateAddedIndex).setValue(timestamp);
  } else if (!hasOtherData && dateValue) {
    // Clear timestamp if it's the only value in the row
    sheet.getRange(row, dateAddedIndex).clearContent();
  }
}
 

Todo: add option for Time column

Done

// Function to add timestamp and time when a new row is added
function addTimestamp(sheet, row, headerDict) {
  const dateAddedIndex = headerDict["Date Added"];
  const timeAddedIndex = headerDict["Time"];
 
  const timestamp = new Date();
  const timeString = timestamp.toLocaleTimeString(); // Format the time
 
  // If the "Date Added" column is empty, add date and time
  if (!sheet.getRange(row, dateAddedIndex).getValue()) {
    sheet.getRange(row, dateAddedIndex).setValue(timestamp);
  }
 
  // If the "Time" column is empty, add only the time
  if (!sheet.getRange(row, timeAddedIndex).getValue()) {
    sheet.getRange(row, timeAddedIndex).setValue(timeString);
  }
}

Template Sheets

7/15/25

General Template

• General template
• Published link

Code.gs

// Function triggered on edit event
function onEdit(e) {
  if (!e) {
    // Simulate the event object for manual testing
    e = { source: SpreadsheetApp.getActiveSpreadsheet() };
  }
 
  const sheet = e.source.getActiveSheet();
  // if (sheet.getName() !== "Sheet1") return; // Only run on "Sheet1"
  const sheetName = sheet.getName(); // Get the name of the active sheet
  const validSheets = ["Sheet1", "Sheet2"]; // Array of valid sheet names
  if (!validSheets.includes(sheetName)) return; // If not in the list, exit function
  Logger.log(sheet);
 
  // Get the header dictionary from the sheet
  const headerDict = getHeaderDict(sheet);
 
  const editedRange = e.range;
  const editedRow = editedRange.getRow();
  const editedColumn = e.range.getColumn();
 
  // Uncomment to insert timestamp 
  addTimestamp(sheet, editedRow, headerDict);
 
  // Auto-sort 
  if (shouldSortOnEdit(sheet, editedRow, editedColumn, headerDict)) {
    sortData(sheet, headerDict, editedRow);
  }
}
 
// Function to create the header dictionary
function getHeaderDict(sheet) {
  const lastRow = sheet.getLastRow();
  const headerRow = 1; // The row containing the headers
 
  // Get the headers from the first row
  const headers = sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0];
 
  // Create a dictionary (object) of headers with their column indexes
  const headerDict = headers.reduce((acc, header, index) => {
    acc[header] = index + 1; // Add 1 because column indexes are 1-based in Apps Script
    return acc;
  }, {});
 
 
  // Convert dictionary to a formatted JSON string for better readability
  const headerDictString = JSON.stringify(headerDict, null, 2);
  Logger.log('Header Dictionary: %s', headerDictString);
  // SpreadsheetApp.getUi().alert("Header Dictionary:\n" + headerDictString);
 
  return headerDict;
}
 
// Auto-add timestamp to indicate when a row was created
function addTimestamp(sheet, row, headerDict) {
  const timestampIndex = headerDict["Timestamp"];
  const lastCol = sheet.getLastColumn();
 
  const rowValues = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
  const timestampValue = rowValues[timestampIndex - 1];
 
  // Check whether rest of row was cleared
  const hasOtherData = rowValues
    .filter((_, index) => index !== (timestampIndex - 1))
    .some(cell => cell !== "" && cell !== null);
 
  if (hasOtherData && !timestampValue) {
    // Add timestamp if there's other data but no timestamp yet
    const timestamp = new Date();
    sheet.getRange(row, timestampIndex).setValue(timestamp);
  } else if (!hasOtherData && timestampValue) {
    // Clear timestamp if it's the only value in the row
    sheet.getRange(row, timestampIndex).clearContent();
  }
}
 
// Auto-sort by Date and  Time (optional)
function sortData(sheet, headerDict) {
  const lastRow = sheet.getLastRow();
  const dateColumn = "Date";
  const timeColumn = "Time";
 
  const dateColumnIndex = headerDict[dateColumn];
  const timeColumnIndex = headerDict[timeColumn];
 
  if (!dateColumnIndex) {
    console.error("The 'Date' column was not found.");
    SpreadsheetApp.getUi().alert("The 'Date' column was not found.");
    return;
  }
 
  // Define the data range starting from row 2
  const dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
 
  // Sort by Date only, or Date + Time if Time exists
  const sortCriteria = [{ column: dateColumnIndex, ascending: true }];
  if (timeColumnIndex) {
    sortCriteria.push({ column: timeColumnIndex, ascending: true });
  }
 
  dataRange.sort(sortCriteria);
}
 
// 7/15/25
// Determine whether to trigger sorting based on the edited column and presence of Time column
function shouldSortOnEdit(sheet, editedRow, editedColumn, headerDict) {
  const dateColumnIndex = headerDict["Date"];
  const timeColumnIndex = headerDict["Time"];
 
  // No Date column found
  if (!dateColumnIndex) {
    return false;
  }
 
  // If Time column exists
  if (timeColumnIndex) {
      // If editing the date and a time already exists
      if (editedColumn === dateColumnIndex){
        return !sheet.getRange(editedRow, timeColumnIndex).isBlank();
      }
    // Otherwise, only sort when Time column is edited
    return editedColumn === timeColumnIndex;
  } else {
    // If no Time column, sort only when Date column is edited
    return editedColumn === dateColumnIndex;
  }
}

Time Tracking Template

• If the sort column can be renamed (e.g. “Date” → “Due Date”), this must be reflected in the SortData() method.

• Length can be shown in hours (unhide Column I) or minutes

• Time Tracking Template link | Published

Code.gs

// Function triggered on edit event
function onEdit(e) {
  if (!e) {
    // Simulate the event object for manual testing
    e = { source: SpreadsheetApp.getActiveSpreadsheet() };
  }
 
  const sheet = e.source.getActiveSheet();
  // if (sheet.getName() !== "Sheet1") return; // Only run on "Sheet1"
  const sheetName = sheet.getName(); // Get the name of the active sheet
  const validSheets = ["Sheet1", "Sheet2"]; // Array of valid sheet names
  if (!validSheets.includes(sheetName)) return; // If not in the list, exit function
  Logger.log(sheet);
 
  // Get the header dictionary from the sheet
  const headerDict = getHeaderDict(sheet);
 
  const editedRange = e.range;
  const editedRow = editedRange.getRow();
  const editedColumn = e.range.getColumn();
 
  // Insert timestamp 
  addTimestamp(sheet, editedRow, headerDict);
 
  // Auto-sort 
  if (shouldSortOnEdit(sheet, editedRow, editedColumn, headerDict)) {
    // SpreadsheetApp.getUi().alert("sortData() called-1");
    sortData(sheet, headerDict, editedRow);
  }
 
  if (["Sheet1"].includes(sheetName) ) {
    updateStatusTimestamps(sheet, editedRow, headerDict);
  }
}
 
// Function to create the header dictionary
function getHeaderDict(sheet) {
  const lastRow = sheet.getLastRow();
  const headerRow = 1; // The row containing the headers
 
  // Get the headers from the first row
  const headers = sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0];
 
  // Create a dictionary (object) of headers with their column indexes
  const headerDict = headers.reduce((acc, header, index) => {
    acc[header] = index + 1; // Add 1 because column indexes are 1-based in Apps Script
    return acc;
  }, {});
 
 
  // Convert dictionary to a formatted JSON string for better readability
  const headerDictString = JSON.stringify(headerDict, null, 2);
  Logger.log('Header Dictionary: %s', headerDictString);
  // SpreadsheetApp.getUi().alert("Header Dictionary:\n" + headerDictString);
 
  return headerDict;
}
 
// Auto-add timestamp to indicate when a row was created
function addTimestamp(sheet, row, headerDict) {
  const timestampIndex = headerDict["Timestamp"];
  const lastCol = sheet.getLastColumn();
 
  const rowValues = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
  const timestampValue = rowValues[timestampIndex - 1];
 
  // Check whether rest of row was cleared
  const hasOtherData = rowValues
    .filter((_, index) => index !== (timestampIndex - 1))
    .some(cell => cell !== "" && cell !== null);
 
  if (hasOtherData && !timestampValue) {
    // Add timestamp if there's other data but no timestamp yet
    const timestamp = new Date();
    sheet.getRange(row, timestampIndex).setValue(timestamp);
  } else if (!hasOtherData && timestampValue) {
    // Clear timestamp if it's the only value in the row
    sheet.getRange(row, timestampIndex).clearContent();
  }
}
 
// Auto-sort by Date and  Time (optional)
function sortData(sheet, headerDict) {
  const lastRow = sheet.getLastRow();
  const dateColumn = "Date";
  const timeColumn = "Time";
  const dateColumnIndex = headerDict[dateColumn];
  const timeColumnIndex = headerDict[timeColumn];
 
  if (!dateColumnIndex) {
    console.error("The 'Date' column was not found.");
    SpreadsheetApp.getUi().alert("The 'Date' column was not found.");
    return;
  }
 
  // Define the data range starting from row 2
  const dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
 
  // Sort by Date only, or Date + Time if Time exists
  const sortCriteria = [{ column: dateColumnIndex, ascending: true }];
  if (timeColumnIndex) {
    sortCriteria.push({ column: timeColumnIndex, ascending: true });
  }
 
    dataRange.sort(sortCriteria);
}
 
// 7/15/25
// Determine whether to trigger sorting based on the edited column and presence of Time column
function shouldSortOnEdit(sheet, editedRow, editedColumn, headerDict) {
  const dateColumnIndex = headerDict["Date"]; // Rename sort columns if necessary
  const timeColumnIndex = headerDict["Time"];
  // SpreadsheetApp.getUi().alert(String(timeColumnIndex ?? 'Time column not found'));
 
 
  // No Date column found
  if (!dateColumnIndex) {
    SpreadsheetApp.getUi().alert("The sort column (e.g. Date) was not found.");
    return false;
  }
 
  // If Time column exists
  if (timeColumnIndex) {
      // If editing the date and a time already exists
      if (editedColumn === dateColumnIndex){
        return !sheet.getRange(editedRow, timeColumnIndex).isBlank();
      }
    // Otherwise, only sort when Time column is edited
    return editedColumn === timeColumnIndex;
  } else {
    // If no Time column, sort only when Date column is edited
    return editedColumn === dateColumnIndex;
  }
}
 
// 7/15/25
function updateStatusTimestamps(sheet, row, headerDict) {
  const statusIndex = headerDict["Status"];
  const dateStartedIndex = headerDict["Date Started"];
  const timeStartedIndex = headerDict["Time Started"];
  const dateSubmittedIndex = headerDict["Date Submitted"];
  const timeSubmittedIndex = headerDict["Time Submitted"];
 
  const statusValue = sheet.getRange(row, statusIndex).getValue();
  const now = new Date();
  const time = new Date(0);
  time.setHours(now.getHours(), now.getMinutes(), now.getSeconds());
  // SpreadsheetApp.getUi().alert(time);
 
  // SpreadsheetApp.getUi().alert("test");
  // SpreadsheetApp.getUi().alert(sheet.getRange(row, timeStartedIndex).getValue());
 
  if (statusValue === "In Progress") {
    if (!sheet.getRange(row, dateStartedIndex).getValue()) {
      sheet.getRange(row, dateStartedIndex).setValue(now);
    }
    if (!sheet.getRange(row, timeStartedIndex).getValue()) {
      sheet.getRange(row, timeStartedIndex).setValue(now);
    }
  }
 
  if (statusValue === "Paused") {
    if (!sheet.getRange(row, dateSubmittedIndex).getValue()) {
      sheet.getRange(row, dateSubmittedIndex).setValue(now);
    }
    if (!sheet.getRange(row, timeSubmittedIndex).getValue()) {
      sheet.getRange(row, timeSubmittedIndex).setValue(now);
    }
  }
 
  if (statusValue === "Complete") {
    if (!sheet.getRange(row, dateSubmittedIndex).getValue()) {
      sheet.getRange(row, dateSubmittedIndex).setValue(now);
    }
    if (!sheet.getRange(row, timeSubmittedIndex).getValue()) {
      sheet.getRange(row, timeSubmittedIndex).setValue(now);
    }
  }
 
if (statusValue === "To Do") {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    "Warning",
    "You are about to clear the started/submitted dates and times for this row. Continue?",
    ui.ButtonSet.YES_NO
  );
 
  if (response === ui.Button.YES) {
    if (sheet.getRange(row, dateStartedIndex).getValue()) {
      sheet.getRange(row, dateStartedIndex).setValue("");
    }
    if (sheet.getRange(row, timeStartedIndex).getValue()) {
      sheet.getRange(row, timeStartedIndex).setValue("");
    }
    if (sheet.getRange(row, dateSubmittedIndex).getValue()) {
      sheet.getRange(row, dateSubmittedIndex).setValue("");
    }
    if (sheet.getRange(row, timeSubmittedIndex).getValue()) {
      sheet.getRange(row, timeSubmittedIndex).setValue("");
    }
  } 
}
}