Untitled
unknown
plain_text
a year ago
5.6 kB
6
Indexable
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" } } }
Editor is loading...
Leave a Comment