Skip to content

Instantly share code, notes, and snippets.

@samsoft00
Last active August 16, 2024 06:16
Show Gist options
  • Save samsoft00/0e07fb5cb46d932483e7968564f1edc7 to your computer and use it in GitHub Desktop.
Save samsoft00/0e07fb5cb46d932483e7968564f1edc7 to your computer and use it in GitHub Desktop.
/**
* Function to extract file.
* TPC Essay Submission Form
* YOUTUBE: https://www.youtube.com/watch?v=uzrVM8abloE
*/
function tpcEassyFormToDoc(e) {
const TPC_DOC_FOLDER = '1yoNu0Yp49cEQ6I2SfWLzerNb535ovscb';
const TPC_DOC_TEMPLATE = '1Nz3eiW1mTI3xZdMz4FE_WNQfCcVKuZFUcfrSmS5b4Vk';
const ALLOW_TIMEZONE = 'America/Chicago';
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var timestampColumn = 1;
var easySubmissionTypeColumn = 3;
var confirmMaxSubmitPersonalColumn = 4;
var confirmMaxSubmitActivityColumn = 5;
var confirmPromptSubmissionColumn = 6;
var submissionForColumn = 7;
var dateScheduleInterviewColumn = 8;
var easySubmissionColumn = 9;
var confirmMaxSubmitOnetimeColumn = 10;
var emailColumn = 11;
var nameColumn = 12;
var confirmMaxSubmitOtherColumn = 13;
var name = sheet.getRange(lastRow, nameColumn).getValue();
var email = sheet.getRange(lastRow, emailColumn).getValue();
var easySubmissionType = sheet.getRange(lastRow, easySubmissionTypeColumn).getValue();
var dateScheduleInterview = sheet.getRange(lastRow, dateScheduleInterviewColumn).getValue();
var easySubmission = sheet.getRange(lastRow, easySubmissionColumn).getValue();
var submissionFor = sheet.getRange(lastRow, submissionForColumn).getValue();
var timestamp = sheet.getRange(lastRow, timestampColumn).getValue();
/*
var confirmMaxSubmitPersonal = sheet.getRange(lastRow, confirmMaxSubmitPersonalColumn).getValue();
var confirmMaxSubmitActivity = sheet.getRange(lastRow, confirmMaxSubmitActivityColumn).getValue();
var confirmPromptSubmission = sheet.getRange(lastRow, confirmPromptSubmissionColumn).getValue();
var confirmMaxSubmitOnetime = sheet.getRange(lastRow, confirmMaxSubmitOnetimeColumn).getValue();
var confirmMaxSubmitOther = sheet.getRange(lastRow, confirmMaxSubmitOtherColumn).getValue();
*/
try {
var templateFile = DriveApp.getFileById(TPC_DOC_TEMPLATE);
var templateResponseFolder = DriveApp.getFolderById(TPC_DOC_FOLDER);
var studentName = name.toString().toUpperCase();
var fileDateString = Utilities.formatDate(timestamp, ALLOW_TIMEZONE, 'MM-dd-YYYY');
var submittedDateTime = Utilities.formatDate(timestamp, ALLOW_TIMEZONE, 'EEE MMM dd, yyyy HH:mm:ss zzz (zzzz)');
var copy = templateFile.makeCopy(`${studentName} - ${fileDateString}`, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId());
var docBody = doc.getBody();
if(Object.is(submissionFor,'Written Feedback')){
submissionFor = 'Yes'; // If Written Feedback
} else {
if(dateScheduleInterview){
var feedbackInterviewDate = Utilities.formatDate(dateScheduleInterview, ALLOW_TIMEZONE, 'EEE MMM dd, yyyy');
submissionFor = `${submissionFor} (${feedbackInterviewDate})`;
}
}
docBody.replaceText('{{name}}', studentName);
docBody.replaceText('{{email}}', email);
docBody.replaceText('{{submission_for}}', submissionFor);
docBody.replaceText('{{type_of_essay}}', easySubmissionType);
docBody.replaceText('{{total_character}}', easySubmission.toString().length);
docBody.replaceText('{{date_submitted}}', submittedDateTime);
docBody.replaceText('{{eassy_submission}}', easySubmission)
/*
docBody.replaceText('{{confirm_max_submit_personal}}', confirmMaxSubmitPersonal);
docBody.replaceText('{{confirm_max_submit_activities}}', confirmMaxSubmitActivity);
docBody.replaceText('{{confirm_prompt_submission}}', confirmPromptSubmission);
docBody.replaceText('{{date_schedule_interview}}', dateScheduleInterview);
docBody.replaceText('{{confirm_max_submit_onetime}}', confirmMaxSubmitOnetime);
docBody.replaceText('{{confirm_max_submit_other}}', confirmMaxSubmitOther);
*/
doc.saveAndClose();
} catch (err) {
console.log({err: err.message})
throw err;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment