อัพเกรดข้อความ LINE OA เดิม ให้ส่งไปพร้อม Quick Reply ด้วย Apps Script

/**
 * Gets an action item by its ID from the appropriate sheet
 * 
 * @param {SpreadsheetApp.Spreadsheet} quickReplySheetID - The spreadsheet
 * @param {string} actionId - The action ID
 * @return {Object} A properly formatted quick reply item
 */
function getActionById(quickReplySheetID, actionId) {
  try {
    // Determine which sheet to look in based on the action ID prefix
    let sheetName;
    if (actionId.startsWith('msg-')) {
      sheetName = 'message';
    } else if (actionId.startsWith('pb-')) {
      sheetName = 'postback';
    } else if (actionId.startsWith('uri-')) {
      sheetName = 'uri';
    } else if (actionId.startsWith('dt-')) {
      sheetName = 'datetimepicker';
    } else if (actionId.startsWith('cam-')) {
      sheetName = 'camera';
    } else if (actionId.startsWith('cr-')) {
      sheetName = 'cameraroll';
    } else if (actionId.startsWith('loc-')) {
      sheetName = 'location';
    } else if (actionId.startsWith('cb-')) {
      sheetName = 'clipboard';
    } else {
      Logger.log(`Unknown action ID prefix: ${actionId}`);
      return null;
    }
    
    const sheet = quickReplySheetID.getSheetByName(sheetName);
    if (!sheet) {
      Logger.log(`Sheet ${sheetName} not found`);
      return null;
    }
    
    const data = sheet.getDataRange().getValues();
    if (data.length <= 1) {
      Logger.log(`Sheet ${sheetName} has no data rows`);
      return null;
    }
    
    const headers = data[0];
    const idColIndex = headers.indexOf('ItemId');
    
    if (idColIndex === -1) {
      Logger.log(`actionId ${actionId} column not found in ${sheetName} sheet`);
      return null;
    }
    
    // Find the action in the sheet
    let actionRow = -1;
    for (let i = 1; i < data.length; i++) {
      if (data[i][idColIndex] === actionId) {
        actionRow = i;
        break;
      }
    }
    
    if (actionRow === -1) {
      Logger.log(`Action with ID ${actionId} not found in ${sheetName} sheet`);
      return null;
    }
    
    // Create the action item based on the sheet type
    return createActionItem(sheetName, data[actionRow], headers);
    
  } catch (error) {
    Logger.log(`Error getting action by ID: ${error.message}`);
    return null;
  }
}

/**
 * Creates a quick reply action item based on the sheet type and row data
 * 
 * @param {string} sheetName - The name of the sheet
 * @param {Array} row - The row data
 * @param {Array} headers - The header row
 * @return {Object} A properly formatted quick reply item
 */
function createActionItem(sheetName, row, headers) {
  try {
    // Basic item structure
    const item = {
      type: 'action',
      action: {}
    };
    
    // Helper function to trim label to 20 characters
    function trimLabel(label) {
      if (label && label.length > 20) {
        Logger.log(`Trimming label from ${label.length} to 20 characters: ${label}`);
        return label.substring(0, 20);
      }
      return label;
    }
    
    // Helper function to format date values based on mode
    function formatDateForMode(dateValue, mode) {
      if (!dateValue) return null;
      
      //Logger.log(`Formatting date value: ${dateValue} for mode: ${mode}`);
      
      try {
        // Parse the date value
        let date;
        if (typeof dateValue === 'string') {
          // Try to parse the date string
          const parts = dateValue.split('-');
          if (parts.length === 3) {
            // Format: YYYY-MM-DD
            const year = parseInt(parts[0], 10);
            const month = parseInt(parts[1], 10) - 1; // Months are 0-indexed in JS
            const day = parseInt(parts[2], 10);
            date = new Date(year, month, day);
          } else {
            date = new Date(dateValue);
          }
        } else if (dateValue instanceof Date) {
          date = dateValue;
        } else {
          date = new Date(dateValue);
        }
        
        if (isNaN(date.getTime())) {
          Logger.log(`Invalid date value: ${dateValue}`);
          return null;
        }
        
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        const hours = String(date.getHours()).padStart(2, '0');
        const minutes = String(date.getMinutes()).padStart(2, '0');
        
        let formattedDate;
        
        if (mode === 'date') {
          formattedDate = `${year}-${month}-${day}`;
        } else if (mode === 'time') {
          formattedDate = `${hours}:${minutes}`;
        } else if (mode === 'datetime') {
          formattedDate = `${year}-${month}-${day}T${hours}:${minutes}`;
        } else {
          Logger.log(`Unknown mode: ${mode}`);
          return null;
        }
        
        //Logger.log(`Formatted date: ${formattedDate}`);
        return formattedDate;
      } catch (e) {
        Logger.log(`Error formatting date: ${e.message}`);
        return null;
      }
    }
    
    // Add common properties based on the sheet type
    switch (sheetName) {
      case 'message':
        // Find column indexes
        const labelCol = headers.indexOf('Label');
        const textCol = headers.indexOf('Text');
        const imageUrlCol = headers.indexOf('ImageURL');
        
        if (labelCol === -1 || textCol === -1 || row[labelCol] === '' || row[textCol] === '') {
          Logger.log(`Missing required fields in message sheet`);
          return null;
        }
        
        item.action = {
          type: 'message',
          label: trimLabel(row[labelCol]),
          text: row[textCol]
        };
        
        // Add imageUrl if available
        if (imageUrlCol !== -1 && row[imageUrlCol]) {
          item.imageUrl = row[imageUrlCol];
        }
        break;
        
      case 'postback':
        // Find column indexes
        const pbLabelCol = headers.indexOf('Label');
        const dataCol = headers.indexOf('Data');
        const displayTextCol = headers.indexOf('DisplayText');
        const pbImageUrlCol = headers.indexOf('ImageURL');
        const inputOptionCol = headers.indexOf('InputOption');
        const fillInTextCol = headers.indexOf('FillInText');
        
        if (pbLabelCol === -1 || dataCol === -1 || row[pbLabelCol] === '' || row[dataCol] === '') {
          Logger.log(`Missing required fields in postback sheet`);
          return null;
        }
        
        item.action = {
          type: 'postback',
          label: trimLabel(row[pbLabelCol]),
          data: row[dataCol]
        };
        
        // Add optional fields if available
        if (displayTextCol !== -1 && row[displayTextCol]) {
          item.action.displayText = row[displayTextCol];
        }
        
        if (pbImageUrlCol !== -1 && row[pbImageUrlCol]) {
          item.imageUrl = row[pbImageUrlCol];
        }
        
        if (inputOptionCol !== -1 && row[inputOptionCol]) {
          item.action.inputOption = row[inputOptionCol];
        }
        
        if (fillInTextCol !== -1 && row[fillInTextCol]) {
          item.action.fillInText = row[fillInTextCol];
        }
        break;
        
      case 'uri':
        // Find column indexes
        const uriLabelCol = headers.indexOf('Label');
        const uriCol = headers.indexOf('URI');
        const uriImageUrlCol = headers.indexOf('ImageURL');
        
        if (uriLabelCol === -1 || uriCol === -1 || row[uriLabelCol] === '' || row[uriCol] === '') {
          Logger.log(`Missing required fields in uri sheet`);
          return null;
        }
        
        item.action = {
          type: 'uri',
          label: trimLabel(row[uriLabelCol]),
          uri: row[uriCol]
        };
        
        // Add imageUrl if available
        if (uriImageUrlCol !== -1 && row[uriImageUrlCol]) {
          item.imageUrl = row[uriImageUrlCol];
        }
        break;
        
      case 'datetimepicker':
        // Find column indexes
        const dtLabelCol = headers.indexOf('Label');
        const dtDataCol = headers.indexOf('Data');
        const modeCol = headers.indexOf('Mode');
        const initialCol = headers.indexOf('Initial');
        const maxCol = headers.indexOf('Max');
        const minCol = headers.indexOf('Min');
        const dtImageUrlCol = headers.indexOf('ImageURL');
        
        if (dtLabelCol === -1 || dtDataCol === -1 || modeCol === -1 || 
            row[dtLabelCol] === '' || row[dtDataCol] === '' || row[modeCol] === '') {
          Logger.log(`Missing required fields in datetimepicker sheet`);
          return null;
        }
        
        const mode = row[modeCol].trim().toLowerCase();
        // Logger.log(`Datetimepicker mode: ${mode}`);
        
        // Validate mode
        if (mode !== 'date' && mode !== 'time' && mode !== 'datetime') {
          Logger.log(`Invalid datetimepicker mode: ${mode}. Must be 'date', 'time', or 'datetime'.`);
          return null;
        }
        
        item.action = {
          type: 'datetimepicker',
          label: trimLabel(row[dtLabelCol]),
          data: row[dtDataCol],
          mode: row[modeCol]
        };
        
        // Add optional fields if available, with proper formatting
        if (initialCol !== -1 && row[initialCol]) {
          const formattedInitial = formatDateForMode(row[initialCol], mode);
          if (formattedInitial) {
            item.action.initial = formattedInitial;
          }
        }
        
        if (maxCol !== -1 && row[maxCol]) {
          const formattedMax = formatDateForMode(row[maxCol], mode);
          if (formattedMax) {
            item.action.max = formattedMax;
          }
        }
        
        if (minCol !== -1 && row[minCol]) {
          const formattedMin = formatDateForMode(row[minCol], mode);
          if (formattedMin) {
            item.action.min = formattedMin;
          }
        }
        
        // Log the formatted datetimepicker action for debugging
        //Logger.log(`Datetimepicker action: ${JSON.stringify(item.action)}`);
        
        if (dtImageUrlCol !== -1 && row[dtImageUrlCol]) {
          item.imageUrl = row[dtImageUrlCol];
        }
        break;
        
      case 'camera':
        // Find column indexes
        const camLabelCol = headers.indexOf('Label');
        const camImageUrlCol = headers.indexOf('ImageURL');
        
        if (camLabelCol === -1 || row[camLabelCol] === '') {
          Logger.log(`Missing required fields in camera sheet`);
          return null;
        }
        
        item.action = {
          type: 'camera',
          label: trimLabel(row[camLabelCol])
        };
        
        // Add imageUrl if available
        if (camImageUrlCol !== -1 && row[camImageUrlCol]) {
          item.imageUrl = row[camImageUrlCol];
        }
        break;
        
      case 'cameraroll':
        // Find column indexes
        const crLabelCol = headers.indexOf('Label');
        const crImageUrlCol = headers.indexOf('ImageURL');
        
        if (crLabelCol === -1 || row[crLabelCol] === '') {
          Logger.log(`Missing required fields in cameraroll sheet`);
          return null;
        }
        
        item.action = {
          type: 'cameraRoll',
          label: trimLabel(row[crLabelCol])
        };
        
        // Add imageUrl if available
        if (crImageUrlCol !== -1 && row[crImageUrlCol]) {
          item.imageUrl = row[crImageUrlCol];
        }
        break;
        
      case 'location':
        // Find column indexes
        const locLabelCol = headers.indexOf('Label');
        const locImageUrlCol = headers.indexOf('ImageURL');
        
        if (locLabelCol === -1 || row[locLabelCol] === '') {
          Logger.log(`Missing required fields in location sheet`);
          return null;
        }
        
        item.action = {
          type: 'location',
          label: trimLabel(row[locLabelCol])
        };
        
        // Add imageUrl if available
        if (locImageUrlCol !== -1 && row[locImageUrlCol]) {
          item.imageUrl = row[locImageUrlCol];
        }
        break;
        
      case 'clipboard':
        // Find column indexes
        const cbLabelCol = headers.indexOf('Label');
        const clipboardTextCol = headers.indexOf('ClipboardText');
        const cbImageUrlCol = headers.indexOf('ImageURL');
        
        if (cbLabelCol === -1 || clipboardTextCol === -1 || 
            row[cbLabelCol] === '' || row[clipboardTextCol] === '') {
          Logger.log(`Missing required fields in clipboard sheet`);
          return null;
        }
        
        item.action = {
          type: 'clipboard',
          label: trimLabel(row[cbLabelCol]),
          clipboardText: row[clipboardTextCol]
        };
        
        // Add imageUrl if available
        if (cbImageUrlCol !== -1 && row[cbImageUrlCol]) {
          item.imageUrl = row[cbImageUrlCol];
        }
        break;
        
      default:
        Logger.log(`Unknown sheet type: ${sheetName}`);
        return null;
    }
    
    return item;
  } catch (error) {
    Logger.log(`Error creating action item: ${error.message}`);
    return null;
  }
}