AppSScript Code

Copy Code

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('YouTube menu')
    .addItem('Insert Headers', 'insertHeaders')
    .addItem('generateTags', 'generateTags')
    .addItem('discription', 'discription')
    .addItem("Upload at youTube   ", "uploadVideosToYouTube")
    .addToUi();
}


function insertHeaders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = ["Video Title", "Video URL", "Tags", "Description"];
 
  // Insert headers across columns A to F
  var headerRange = sheet.getRange(1, 1, 1, headers.length);
  headerRange.setValues([headers]);
  headerRange.setFontWeight("bold"); // Make the header row bold
}








function generateTags() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const titlesRange = sheet.getRange('A2:A' + lastRow);
  const titles = titlesRange.getValues();
  const tagsRange = sheet.getRange('C2:C' + lastRow);
  const existingTags = tagsRange.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(', '); // Extract tags, remove numbers and dots after #
        const formattedTags = tags.replace(/#\s*#/g, "#"); // Remove duplicate # symbols
        sheet.getRange('C' + (index + 2)).setValue(formattedTags);
      } else {
        Logger.log('Failed to get a response from ChatGPT for title: ' + title);
      }
    } else {
      Logger.log('Tags already exist for title: ' + title);
    }
  });
}




function discription() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const titlesRange = sheet.getRange('A2:A' + lastRow);
  const titles = titlesRange.getValues();
  const descriptionsRange = sheet.getRange('D2:D' + lastRow);
  const existingDescriptions = descriptionsRange.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(); // Trim any extra spaces
        sheet.getRange('D' + (index + 2)).setValue(description);
      } else {
        Logger.log('Failed to get a response from ChatGPT for title: ' + title);
      }
    } else {
      Logger.log('Description already exists for title: ' + title);
    }
  });
}




/**
 * Sends a request to the ChatGPT API with the given prompt.
 * @param {string} prompt The prompt to send to ChatGPT.
 * @return {string} The response from ChatGPT, or null if an error occurred.
 */
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: ' + e);
    return null;
  }
}








function uploadVideosToYouTube() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();


  for (var i = 2; i <= lastRow; i++) {
    // Get data from cells in columns A to F
    var title = sheet.getRange("A" + i).getValue(); // Video Title
    var url = sheet.getRange("B" + i).getValue(); // Video URL
    var tags = sheet.getRange("C" + i).getValue().split(','); // Video Tags
    var description = sheet.getRange("D" + i).getValue(); // Video Description


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


    // Extract file ID from URL
    var fileId = getIdFromUrl(url);


    if (!fileId) {
      Logger.log('Invalid YouTube URL at row ' + i);
      continue; // Skip to the next iteration
    }


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


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


    // Create metadata for YouTube upload
    var metadata = {
      snippet: {
        title: title,
        description: description,
        tags: tags
      },
      status: {
        privacyStatus: 'private' // You can set privacy status accordingly
      }
    };


    try {
      // Upload video to YouTube
      var 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);
    }
  }
}


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