Untitled

function onFormSubmit(e) {
  var formResponsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
  var databaseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Database');
  var lastRow = formResponsesSheet.getLastRow();
  
  // Fetch the latest form response
  var response = formResponsesSheet.getRange(lastRow, 1, 1, formResponsesSheet.getLastColumn()).getValues()[0];

  // Define indices for the form response fields
  var writerName = response[1]; // Writer Name (Column B)
  var blogTitle = response[2]; // Blog Title (Column C)
  var category = response[3]; // Category (Column D)
  var wordLimit = response[4]; // Word Limit (Column E)
  var deadline = response[5]; // Deadline (Column F)
  
  // Find the first empty row in the database to populate the data
  var dataRange = databaseSheet.getDataRange();
  var data = dataRange.getValues();
  var targetRow = -1;

  for (var i = 1; i < data.length; i++) {
    if (!data[i][2]) { // Checking if the Writer (Column C) is empty
      targetRow = i + 1; // Row indices start from 1 in Google Sheets
      break;
    }
  }

  if (targetRow === -1) {
    throw new Error('No empty row found in the Database sheet.');
  }

  // Populate the existing row in the Database sheet
  databaseSheet.getRange(targetRow, 2).setValue(category); // Column B
  databaseSheet.getRange(targetRow, 3).setValue(writerName); // Column C
  databaseSheet.getRange(targetRow, 7).setValue(deadline); // Column G
  databaseSheet.getRange(targetRow, 8).setValue(blogTitle); // Column H
  databaseSheet.getRange(targetRow, 10).setValue(wordLimit); // Column J
  databaseSheet.getRange(targetRow, 17).setValue('yes'); // Column Q (Trigger)

  // Send the blog assignment email and update the link
  sendBlogAssignmentEmails(targetRow);
}

// Function to send blog assignment emails
function sendBlogAssignmentEmails(targetRow) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Database');
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    
    var trigger = row[16]; // Column Q
    if (trigger.toLowerCase() === 'yes') {
      var writerName = row[2]; // Column C
      var writerEmail = row[3]; // Column D
      var category = row[1]; // Column B
      var blogTitle = row[7]; // Column H
      var wordLimit = row[9]; // Column J
      var ppw = row[8]; // Column I
      var deadline = row[6]; // Column G
      var totalPay = row[10]; // Column K
      var instructions = row[13]; // Column N
      var referenceBlog = row[14]; // Column O

      // Create a new Google Doc for writing
      var doc = DocumentApp.create(blogTitle + ' - ' + writerName);
      var docId = doc.getId();
      var docUrl = doc.getUrl();
      
      // Set permissions to "Anyone with the link can edit"
      DriveApp.getFileById(docId).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);

      // Update the Google Sheet with the new doc link
      sheet.getRange(i + 1, 16).setValue(docUrl); // Column P is 16th column (index 15)

      // Add the blog title to the document with TITLE formatting and bold
      var body = doc.getBody();
      var titleParagraph = body.appendParagraph(blogTitle);
      titleParagraph.setHeading(DocumentApp.ParagraphHeading.TITLE);
      titleParagraph.setBold(true);

      // Introduce a delay to ensure the document is fully created and shared
      Utilities.sleep(2000); // 2 second delay

      var subject = "New Blog Assignment: " + blogTitle;
      var message = `
      <p>Hey ${writerName},</p>
      <p>Greetings!</p>
      <p>We're thrilled to assign you an exciting new blog to research and write.</p>
      <p><strong>Blog Title:</strong> ${blogTitle}<br>
      <strong>Category:</strong> ${category}<br>
      <strong>Word Limit:</strong> ${wordLimit}<br>
      <strong>PPW:</strong> ${ppw}<br>
      <strong>Deadline:</strong> ${deadline}<br>
      <strong>Total Pay:</strong> ${totalPay} INR (If Word Count is Met)</p>
      <p><strong>Instructions Doc:</strong><br>
      <a href="${instructions}">${instructions}</a></p>
      <p><strong>Reference Blog:</strong><br>
      <a href="${referenceBlog}">${referenceBlog}</a></p>
      <p><strong>Writing Doc Link:</strong><br>
      <a href="${docUrl}">${docUrl}</a></p>
      <p>Note: Please ensure that the word count meets the specified requirement, excluding titles, in order to receive the previously discussed compensation. As your payment is calculated on a per-word basis, a lower word count will result in reduced payment. Your remuneration will increase as you gain seniority with us. Once your blogs consistently adhere to our format requirements with minimal revisions, you will experience a gradual increase in pay.</p>
      <p>We're delighted to have you on board! Happy writing!</p>
      <p>Feel free to reach out with any queries.</p>
      <p>Best Regards,<br>
      LifeCraftDaily</p>
      `;

      MailApp.sendEmail({
        to: writerEmail,
        subject: subject,
        htmlBody: message
      });

      // Update the Google Sheet with the new doc link after sending the email
      sheet.getRange(targetRow, 15).setValue(docUrl); // Column P is 15th column (index 14)
      Logger.log('Successfully updated the link in the Database sheet.');
      sheet.getRange(i + 1, 17).setValue('sent'); // Update the status in column Q to "sent"
    }
  }
}