Google Appscript format batchs of tabs within a Google Sheet

/*
The success of the entire script, especially the formatting, hinges on the correct setup of the getColumnFormats() function. It's essential that the column names and their corresponding formats in this function accurately reflect the actual data structure in your sheets for the script to work effectively.

The script below is based on a google sheet that contains hotel reservations data but the column order and the cell formatting was not correct, the issue was happening with hundreds of tabs within the same sheet, modify the script according to your needs:

1. processSheets()
This is the main function that initiates the processing of all sheets in the active spreadsheet. It performs several key tasks:

- Retrieves all the sheets in the spreadsheet.
- Creates or accesses the 'Formatting Logs' sheet for tracking progress.
- Processes each sheet (up to 30 at a time for resource management).
- Logs the processing status (success or error) for each sheet.
- Displays a notification summarizing the number of sheets successfully processed and those with errors.

2. processSingleSheet(sheet, columnFormats)
Processes an individual sheet by:

- Checking if the sheet has any columns and rows.
- Sorting the columns alphabetically based on headers.
- Writing the sorted data back to the sheet.
- Applying formatting to each column based on predefined rules.
- Returning the status of processing (success or error with notes).

3. applyFormat(range, formatInfo)
- Applies formatting to a given range in a sheet. It uses the format information specified in the formatInfo parameter. The function switches between different types of data (date, text, number, currency) and applies the corresponding format.

4. logStatus(logSheet, sheet, status)
- Logs the processing status of each sheet in the 'Formatting Logs' sheet. It records the sheet name, a hyperlink to the sheet, the timestamp of processing, and notes on the status (success or error).

5. setupLogSheet(logSheet)
- Sets up the initial headers in the 'Formatting Logs' sheet for tracking. Headers include "Sheet Name", "Hyperlink", "Timestamp", "Status", and "Status Notes".

6. isSheetProcessed(logSheet, sheetName)
- Checks if a sheet has already been processed successfully. It prevents reprocessing of sheets that are already formatted.

7. getColumnFormats()
- Crucial for the entire script, this function returns a hardcoded object mapping column names to their respective data types and formats. It is essential that the user manually matches these names and formats with the actual column names present in the sheets. Failure to correctly match these will result in improper or failed formatting. The formats for each type of data (like date, text, number, currency) need to be predefined here.

If you need a test spreadsheet you can generate one using the script that I have written here: https://pastebin.com/nbhP4WxB


*/

function processSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var columnFormats = getColumnFormats();

  // Create or get the 'Formatting Logs' sheet
  var logSheet = ss.getSheetByName("Formatting Logs") || ss.insertSheet("Formatting Logs");
  // Set up log sheet headers
  setupLogSheet(logSheet);

  var processedCount = 0;
  var errorCount = 0;
  var successCount = 0;

  // Process each sheet
  sheets.forEach(function(sheet) {
    if (sheet.getName() !== "Formatting Logs" && !isSheetProcessed(logSheet, sheet.getName())) {
      var status = processSingleSheet(sheet, columnFormats);
      logStatus(logSheet, sheet, status);

      if (status.error) {
        errorCount++;
      } else {
        successCount++;
      }

      processedCount++;
      if (processedCount >= 30) {
        // Stop processing after 30 sheets
        return;
      }
    }
  });

  // Show notification after processing
  SpreadsheetApp.getUi().alert(
    'Processing completed. ' +
    'Success: ' + successCount + ', ' +
    'Errors: ' + errorCount
  );
}

function processSingleSheet(sheet, columnFormats) {
  var status = { error: false, notes: [] };
  var lastColumn = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();

  // Check if the sheet has columns and rows
  if (lastColumn === 0 || lastRow === 0) {
    status.error = true;
    status.notes.push("No columns or rows found in sheet.");
    return status;
  }

  // Get headers and sort them along with their data
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var data = sheet.getRange(2, 1, lastRow - 1, lastColumn).getValues();

  // Create a combined array of headers and data for sorting
  var combined = headers.map(function(header, index) {
    return [header].concat(data.map(function(row) {
      return row[index];
    }));
  });

  // Sort the combined array alphabetically based on headers
  combined.sort(function(a, b) {
    return a[0].localeCompare(b[0]);
  });

  // Write the sorted data back to the sheet
  for (var col = 0; col < combined.length; col++) {
    sheet.getRange(1, col + 1, lastRow).setValues(combined[col].map(function(cell, index) {
      return [cell];
    }));
  }

  // Apply formats to the sorted columns and check for errors
  combined.forEach(function(column, index) {
    var header = column[0];
    if (columnFormats[header]) {
      try {
        var range = sheet.getRange(2, index + 1, lastRow - 1);
        applyFormat(range, columnFormats[header]);
      } catch (e) {
        status.error = true;
        status.notes.push("Error in column: " + header + " - " + e.message);
      }
    }
  });

  // Return processing status
  return status;
}

function applyFormat(range, formatInfo) {
  // Apply format based on type and specific format rules
  switch (formatInfo.type) {
    case "date":
      range.setNumberFormat(formatInfo.format);
      break;
    case "text":
      // Text formatting can be added here
      break;
    case "number":
      range.setNumberFormat(formatInfo.format);
      break;
    case "currency":
      range.setNumberFormat(formatInfo.format);
      break;
    // Add more cases as needed
  }
}

function logStatus(logSheet, sheet, status) {
  var timeStamp = new Date();
  var sheetUrl = sheet.getParent().getUrl() + "#gid=" + sheet.getSheetId();
  var statusText = status.error ? "Error" : "Success";
  var notesText = status.notes.join("\n");

  // Append log entry
  logSheet.appendRow([sheet.getName(), sheetUrl, timeStamp, statusText, notesText]);
}

function setupLogSheet(logSheet) {
  // Setup headers for the log sheet
  logSheet.getRange("A1:E1").setValues([["Sheet Name", "Hyperlink", "Timestamp", "Status", "Status Notes"]]);
}

function isSheetProcessed(logSheet, sheetName) {
  // Check if the sheet is already processed
  var data = logSheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === sheetName && data[i][3] === "Success") {
      return true;
    }
  }
  return false;
}

function getColumnFormats() {
  // Hardcoded column names and their formats
  return {
    "Date": { type: "date", format: "MM/dd/yyyy" },
    "Name": { type: "text", format: "none" },
    "Room": { type: "number", format: "0" },
    "Price": { type: "currency", format: "$#,##0.00" },
    "Check-in": { type: "date", format: "MM/dd/yyyy" },
    "Check-out": { type: "date", format: "MM/dd/yyyy" },
    // Add more column formats as needed
  };
}