How to create Apps Script for updating year values in date-time cells across multiple sheets

[henryg]

I’m working with several Google Sheets that contain date-time values in the format mm/dd/yyyy H:mm. I need to bulk update these cells so that all 2024 dates become 2025 dates, but with one day subtracted from the original date. The time portion should remain unchanged.

For instance, if I have a cell showing 08/21/2024 6:00, it should be updated to 08/20/2025 6:00. Similarly, 12/15/2024 14:30 would become 12/14/2025 14:30.

Can someone help me write a Google Apps Script function that will scan through all my spreadsheets and perform this date transformation automatically? I have quite a few sheets to update manually and would prefer an automated solution.

[ExcitedGamer85]

Did something similar six months ago across dozens of sheets. The tricky part was edge cases - month boundaries and leap years that other methods missed. Here’s what worked: I built a master function that hits each spreadsheet file, then processes sheets one by one. The trick is using getRangeList() to find datetime cells instead of scanning everything. I filtered for cells with forward slashes and colons to narrow it down. For the actual changes, I manually parsed each datetime string with split() on both date and time parts. Way more control than Date objects, especially with different locale formatting. After bumping the year and dropping the day, I rebuilt the string before writing it back. Watch out for dates like 01/01/2024 - subtracting a day should roll back to December 2023, not 2025. And definitely test this on a copy first since there’s no undo for batch ops.

[opalEcho]

Google Apps Script works but gets messy fast with multiple sheets and complex date operations. You’ll end up with loops inside loops, handling different date formats, and debugging timezone headaches.

I’ve dealt with similar data transformations across multiple Google Sheets. Instead of fighting Apps Script, I built a workflow in Latenode that’s way cleaner.

Set up a flow that connects to your sheets, reads all datetime cells, applies your logic (change year to 2025 and subtract one day), then writes everything back. You can run it across multiple sheets at once without complex loops.

Latenode handles date parsing automatically - no timezone conversions or format headaches. Plus you can schedule it for future updates.

Whole setup takes 15 minutes vs hours debugging Apps Script. Check it out at https://latenode.com

[sofiap]

The Problem:

You’re trying to update date-time values in multiple Google Sheets, changing all 2024 dates to 2025 dates while subtracting one day. You want an automated solution using Google Apps Script to handle this bulk update across numerous spreadsheets, preserving the time component of the date-time values.

Understanding the “Why” (The Root Cause):

Manually updating date-time values across many spreadsheets is tedious and error-prone. The core challenge is efficiently processing date-time data, handling potential edge cases (like January 1st), and performing batch updates to avoid timeouts or individual cell modifications. Using Google Apps Script allows for automated, batch processing of the data within the Google Sheets environment, improving efficiency and reducing the risk of manual errors. Direct manipulation of Date objects within Apps Script provides better date arithmetic compared to string manipulation, ensuring accurate results.

Step-by-Step Guide:

1. Create and Deploy the Google Apps Script: This script iterates through each spreadsheet, then each sheet, finding and updating date-time values. It handles edge cases and performs batch updates for efficiency.

function updateDates() {
  // Get all spreadsheets (replace with a more specific selection if needed)
  let spreadsheets = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetFiles();

  spreadsheets.forEach(spreadsheet => {
    let ss = SpreadsheetApp.openById(spreadsheet.getId());
    let sheets = ss.getSheets();

    sheets.forEach(sheet => {
      // Get the data range (adjust as needed)
      let range = sheet.getDataRange();
      let values = range.getValues();
      let updatedValues = [];

      values.forEach(row => {
        let updatedRow = [];
        row.forEach(cell => {
          if (typeof cell === 'object' && cell instanceof Date) { //Check if the cell is a date object.
            let year = cell.getFullYear();
            let month = cell.getMonth();
            let day = cell.getDate();
            let hours = cell.getHours();
            let minutes = cell.getMinutes();
            let seconds = cell.getSeconds();
            let milliseconds = cell.getMilliseconds();

            if (year === 2024) {
              //Handle edge case for January 1st
              let newDate = new Date(2025, month, day -1, hours, minutes, seconds, milliseconds);
              if(newDate.getDate() === 0){
                  newDate.setDate(31);
                  newDate.setMonth(newDate.getMonth()-1);
              }
              updatedRow.push(newDate);
            } else {
              updatedRow.push(cell);
            }
          } else {
            updatedRow.push(cell);
          }
        });
        updatedValues.push(updatedRow);
      });
      range.setValues(updatedValues);
    });
  });
}

2. Authorize the Script: Run the script. You’ll be prompted to authorize it to access your Google Sheets.

3. Test Thoroughly: Run the script on a copy of your spreadsheets first to avoid unintended data loss. Carefully check the results in the test copy before applying it to your original data.

Common Pitfalls & What to Check Next:

• Incorrect Date Format: Ensure your date-time values are actually recognized as Date objects by Google Sheets. If they’re formatted as text, the script might not work correctly.
• Locale Settings: The script assumes your dates are in a format understood by the spreadsheet’s locale settings.
• Large Datasets: For very large spreadsheets, you might encounter timeouts. Consider processing the data in smaller batches (e.g., row by row).
• Error Handling: In a production environment, add more robust error handling (e.g., try...catch blocks) to handle potential issues gracefully. Log any errors to aid debugging.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!