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

// 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

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