YouTube Automation: Publish Videos Directly from Google Sheets using Free App Script

// ------------------------------
// YouTube Automation Menu Script
// ------------------------------

// Add custom menu on spreadsheet open
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('YouTube menu')
    .addItem('Insert Headers', 'insertHeaders')
    .addItem('Generate Tags', 'generateTags')
    .addItem('Generate Description', 'discription')
    .addItem('Upload to YouTube', 'uploadVideosToYouTube')
    .addToUi();
}

// ------------------------------
// Insert header row in the sheet
// ------------------------------
function insertHeaders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = ["Video Title", "Video URL", "Tags", "Description"];

  // Insert headers in the first row
  var headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setValues([headers]);
  headerRange.setFontWeight("bold"); // Make headers bold
}

// ------------------------------
// Generate YouTube tags using ChatGPT
// ------------------------------
function generateTags() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const titles = sheet.getRange('A2:A' + lastRow).getValues();
  const existingTags = sheet.getRange('C2:C' + lastRow).getValues();

  titles.forEach((title, index) => {
    if (title[0] && !existingTags[index][0]) {
      const prompt = `Generate 5 relevant tags for the following title: "${title}"`;
      const response = getChatGPTResponse(prompt);

      if (response) {
        const lines = response.split('\n');
        const tags = lines.slice(0, 5)
          .map(tag => "#" + tag.trim().replace(/[\d.]+/g, ''))
          .join(', ');
        const formattedTags = tags.replace(/#\s*#/g, "#"); // Remove duplicate # symbols
        sheet.getRange('C' + (index + 2)).setValue(formattedTags);
      } else {
        Logger.log('Failed to get a response for title: ' + title);
      }
    }
  });
}

// ------------------------------
// Generate YouTube descriptions using ChatGPT
// ------------------------------
function discription() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const titles = sheet.getRange('A2:A' + lastRow).getValues();
  const existingDescriptions = sheet.getRange('D2:D' + lastRow).getValues();

  titles.forEach((title, index) => {
    if (title[0] && !existingDescriptions[index][0]) {
      const prompt = `Generate a short description for the following title: "${title}"`;
      const response = getChatGPTResponse(prompt);

      if (response) {
        const description = response.trim();
        sheet.getRange('D' + (index + 2)).setValue(description);
      } else {
        Logger.log('Failed to get a description for title: ' + title);
      }
    }
  });
}

// ------------------------------
// Function to call ChatGPT API
// ------------------------------
function getChatGPTResponse(prompt) {
  const apiKey = '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';
  const url = 'https://api.openai.com/v1/chat/completions';
  const payload = {
    "model": "gpt-3.5-turbo",
    "messages": [
      { "role": "system", "content": "I am your lovely assistant" },
      { "role": "user", "content": prompt }
    ]
  };

  const options = {
    'method': 'post',
    'payload': JSON.stringify(payload),
    'headers': {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer ' + apiKey
    }
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());
    return data.choices[0].message.content.trim();
  } catch (e) {
    Logger.log('Error calling ChatGPT API: ' + e);
    return null;
  }
}

// ------------------------------
// Upload videos to YouTube
// ------------------------------
function uploadVideosToYouTube() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();

  for (let i = 2; i <= lastRow; i++) {
    const title = sheet.getRange("A" + i).getValue();
    const url = sheet.getRange("B" + i).getValue();
    const tags = sheet.getRange("C" + i).getValue().split(',');
    const description = sheet.getRange("D" + i).getValue();

    Logger.log('Processing video at row ' + i + ' with URL: ' + url);

    const fileId = getIdFromUrl(url);
    if (!fileId) {
      Logger.log('Invalid YouTube URL at row ' + i);
      continue;
    }

    Logger.log('Extracted file ID: ' + fileId);

    // Get video file from Drive
    const videoFile = DriveApp.getFileById(fileId);
    const videoBlob = videoFile.getBlob();

    // Metadata for YouTube
    const metadata = {
      snippet: {
        title: title,
        description: description,
        tags: tags
      },
      status: {
        privacyStatus: 'private'
      }
    };

    try {
      const youtube = YouTube.Videos.insert(metadata, 'snippet,status', videoBlob);
      Logger.log('Video uploaded successfully! Video ID: ' + youtube.id);
    } catch (error) {
      Logger.log('Error uploading video for title ' + title + ': ' + error);
    }
  }
}

// ------------------------------
// Extract file ID from Google Drive URL
// ------------------------------
function getIdFromUrl(url) {
  const match = url.match(/[-\w]{25,}/);
  return match ? match[0] : null;
}