Skip to content

Instantly share code, notes, and snippets.

@portableant

portableant/code.js

Created May 18, 2021
Embed
What would you like to do?
Google action script for parsing sheets data to docs template replacements
function createDocument() {
// Get the headers from a sheet with the sheet id number
var headers = Sheets.Spreadsheets.Values.get('1-3du2O3thRu4Ynj_6ShC0tajxjXOiAufA68Uq_z_F6M', 'A1:AU1');
// Define the area from which to pull data. Assume 200 rows
var starters = Sheets.Spreadsheets.Values.get('1-3du2O3thRu4Ynj_6ShC0tajxjXOiAufA68Uq_z_F6M', 'A2:AU200');
// Define your google document template id number
var templateId = '1tSMgb6m7IfbhSdAeGx2tqYS5fDG5uRyFBVEfmhPUDDA';
for(var i = 0; i < starters.values.length; i++){
console.log(starters.values[i])
// Assemble the variables (remember first column is 0 and not 1
var timestamp = starters.values[i][0];
var email = starters.values[i][1];
var salutation = starters.values[i][2];
var first_name = starters.values[i][3];
var last_name = starters.values[i][4];
var dob = starters.values[i][5];
var previous_employee = starters.values[i][6];
var previous_roles = starters.values[i][7];
var known_crsid = starters.values[i][8];
var had_crsid = starters.values[i][9];
var job_title = starters.values[i][10];
var department = starters.values[i][11];
var line_managers = starters.values[i][12];
var line_managers_email = starters.values[i][13];
var line_managers_phone = starters.values[i][14];
var employment_type = starters.values[i][15];
var replace_role = starters.values[i][16];
var which_role_replace = starters.values[i][17];
var slt_smt_approval = starters.values[i][18];
var ict_equipment_in_place = starters.values[i][19];
var network_number_known = starters.values[i][20];
var network_port_number = starters.values[i][21];
var ict_equipment_required = starters.values[i][22];
var ict_budget_code = starters.values[i][23];
var accessibility_requirements_yes_no = starters.values[i][24];
var accessibility_needs = starters.values[i][25];
var museum_location = starters.values[i][26];
var room_number = starters.values[i][27];
var furniture_in_place = starters.values[i][28];
var new_furniture_required = starters.values[i][29];
var accessibility_changes_required = starters.values[i][30];
var adjustments_required = starters.values[i][31];
var existing_phone_number = starters.values[i][32];
var existing_pc_number = starters.values[i][33];
var drives_required = starters.values[i][34];
var mailing_lists = starters.values[i][35];
var specialist_software = starters.values[i][36];
var adobe_code = starters.values[i][37];
var additional_computing_needs = starters.values[i][38];
var start_date = starters.values[i][39];
var end_date = starters.values[i][40];
var keys_yes_no = starters.values[i][41];
var keys_areas = starters.values[i][42];
var access_yes_no = starters.values[i][43];
var access_areas = starters.values[i][44];
var out_yes_no = starters.values[i][45];
var out_hours_details = starters.values[i][46];
// Set up a file name
var filename = 'New starter request: ' + last_name + ' ' + timestamp;
console.log(filename)
// Check if a file exists before continuing - this checks your entire drive!
var file = DriveApp.getFilesByName(filename)
var chk = file.hasNext()
console.log(chk)
if (chk === false) {
//Make a copy of the template file
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
console.log(documentId)
//Rename the copied file
DriveApp.getFileById(documentId).setName( filename );
//Get the document body as a variable
var body = DocumentApp.openById(documentId).getBody();
//Replace the variables
body.replaceText('##first_name##', first_name)
body.replaceText('##last_name##', last_name)
body.replaceText('##salutation##', salutation)
body.replaceText('##dob##', dob)
body.replaceText('##previous_employee##', previous_employee)
body.replaceText('##previous_roles##', previous_roles)
body.replaceText('##had_crsid##', had_crsid)
body.replaceText('##known_crsid##', known_crsid)
body.replaceText('##job_title##', job_title)
body.replaceText('##department##', department)
body.replaceText('##line_managers##', line_managers)
body.replaceText('##line_managers_email##', line_managers_email)
body.replaceText('##line_managers_phone##', line_managers_phone)
body.replaceText('##employment_type##', employment_type)
body.replaceText('##replace_role##', replace_role)
body.replaceText('##which_role_replace##', which_role_replace)
body.replaceText('##slt_smt_approval##', slt_smt_approval)
body.replaceText('##ict_equipment_in_place##', ict_equipment_in_place)
body.replaceText('##network_number_known##', network_number_known)
body.replaceText('##network_port_number##', network_port_number)
body.replaceText('##ict_equipment_required##', ict_equipment_required)
body.replaceText('##ict_budget_code##', ict_budget_code)
body.replaceText('##accessibility_requirements_yes_no##', accessibility_requirements_yes_no)
body.replaceText('##accessibility_needs##', accessibility_needs)
body.replaceText('##museum_location##', museum_location)
body.replaceText('##room_number##', room_number)
body.replaceText('##furniture_in_place##', furniture_in_place)
body.replaceText('##new_furniture_required##', new_furniture_required)
body.replaceText('##accessibility_changes_required##', accessibility_changes_required)
body.replaceText('##adjustments_required##', adjustments_required)
body.replaceText('##existing_phone_number##', existing_phone_number)
body.replaceText('##existing_pc_number##', existing_pc_number)
body.replaceText('##drives_required##', drives_required)
body.replaceText('##mailing_lists##', mailing_lists)
body.replaceText('##specialist_software##', specialist_software)
body.replaceText('##adobe_code##', adobe_code)
body.replaceText('##additional_computing_needs##', additional_computing_needs)
body.replaceText('##start_date##', start_date)
body.replaceText('##end_date##', end_date)
body.replaceText('##keys_yes_no##', keys_yes_no)
body.replaceText('##keys_areas##', keys_areas)
body.replaceText('##access_yes_no##', access_yes_no)
body.replaceText('##access_areas##', access_areas)
body.replaceText('##out_yes_no##', out_yes_no)
body.replaceText('##out_hours_details##', out_hours_details)
// Move the file to the folder you want it in
docFile = DriveApp.getFileById( documentId );
DriveApp.getFolderById('0AH7b-vYL2mHTUk9PVA').addFile( docFile );
// Remove file from folder
DriveApp.getRootFolder().removeFile(docFile);
MailApp.sendEmail({
to: "",
subject: "Site access request submitted",
htmlBody: '<p>A new starter access form has been created by and can be retrieved from <a href="https://docs.google.com/document/d/' + documentId +'">found as '
+ filename + '</a></p><p>Have a good day.</p>',
});
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment