epg -> drive

function exportEPGToXML() {
  try {
    // Get the active spreadsheet and Sheet2
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = spreadsheet.getSheetByName('Sheet2');
    if (!sheet) {
      throw new Error('Sheet2 not found in the spreadsheet');
    }

    // Get all data from Sheet2 (including headers)
    const dataRange = sheet.getDataRange();
    const data = dataRange.getValues();

    if (data.length <= 1) {
      throw new Error('No data found in Sheet2');
    }

    // Validate headers
    const headers = data[0];
    const expectedHeaders = ['Channel', 'Start Time', 'End Time', 'Title', 'Description'];
    if (!expectedHeaders.every((header, index) => header === headers[index])) {
      throw new Error('Unexpected headers in Sheet2. Expected: ' + expectedHeaders.join(', '));
    }

    // Create XML structure
    const xmlNamespace = XmlService.getNamespace(''); // Default namespace
    const root = XmlService.createElement('tv').setNamespace(xmlNamespace);

    // Process each row (skip header row)
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const channel = row[0] || '';
      const start = row[1] || '';
      const stop = row[2] || '';
      const title = row[3] || '';
      const desc = row[4] || '';

      // Skip empty rows
      if (!channel && !start && !stop && !title && !desc) continue;

      // Create programme element
      const programme = XmlService.createElement('programme')
        .setNamespace(xmlNamespace)
        .setAttribute('channel', channel)
        .setAttribute('start', start)
        .setAttribute('stop', stop);

      // Add title element
      const titleElement = XmlService.createElement('title')
        .setNamespace(xmlNamespace)
        .setText(title);
      programme.addContent(titleElement);

      // Add description element
      const descElement = XmlService.createElement('desc')
        .setNamespace(xmlNamespace)
        .setText(desc);
      programme.addContent(descElement);

      // Add programme to root
      root.addContent(programme);
    }

    // Create XML document
    const document = XmlService.createDocument(root);
    const xmlString = XmlService.getPrettyFormat().format(document);

    // Save to Google Drive
    const fileName = 'epg_name.xml';
    const folder = DriveApp.getRootFolder(); // Saves to root of Google Drive; change if needed
    const existingFiles = folder.getFilesByName(fileName);

    // Delete existing file if it exists
    while (existingFiles.hasNext()) {
      const file = existingFiles.next();
      file.setTrashed(true);
    }

    // Create new file
    folder.createFile(fileName, xmlString, MimeType.PLAIN_TEXT);

    Logger.log(`EPG data exported successfully to ${fileName} in Google Drive`);
    SpreadsheetApp.getUi().alert(`EPG data exported successfully to ${fileName} in Google Drive`);

  } catch (error) {
    Logger.log("Error: " + error.toString());
    SpreadsheetApp.getUi().alert("Error exporting EPG to XML: " + error.toString());
  }
}