Closed echolabstech closed 5 years ago
[ ] I wrote these code for sending email automatically, we need to copy past these code in spreadsheet - tools - script editor and run it in Coderbunker's drive. The spread sheet should have the content (first row and tab name) in following pictures. // function sendNew(){ var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data"); var templetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("template"); var templateSubject = templetSheet.getRange(1, 2).getValue(); var templateBody = templetSheet.getRange(2, 2).getValue();
for (var i =2; i<= dataSheet.getLastRow(); i++){ var name = dataSheet.getRange(i, 1).getValue(); var emailAddress = dataSheet.getRange(i, 2).getValue(); var status = dataSheet.getRange(i, 3).getValue();
var subject = templateSubject.replace("<
if (status == ""){ MailApp.sendEmail(emailAddress, subject, body); dataSheet.getRange(i,3).setValue("Email Sent"); } } }
ScriptApp.newTrigger("sendNew") .timeBased() .everyMinutes(1) .create(); //
We only need the following code to send email automatically. For checking new entries, no need to use code, we can use the setting in Google script, as shown in following picture: // function sendNew(){ var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data"); var templetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("template"); var templateSubject = templetSheet.getRange(1, 2).getValue(); var templateBody = templetSheet.getRange(2, 2).getValue();
for (var i =2; i<= dataSheet.getLastRow(); i++){ var name = dataSheet.getRange(i, 1).getValue(); var emailAddress = dataSheet.getRange(i, 2).getValue(); var status = dataSheet.getRange(i, 3).getValue();
var subject = templateSubject.replace("<>", name); var body = templateBody.replace("<>", name);
if (status == ""){ MailApp.sendEmail(emailAddress, subject, body); dataSheet.getRange(i,3).setValue("Email Sent"); } } } //
This is redundant work to what @ancagabriela has done internally for the script to onboard people.
The full onboarding procedure is described here:
https://docs.google.com/document/d/1OskzP7OSv1wWe212Qgz_MpNTdNQcpgQbZAYKxjX12uA/edit#
We send out this PDF at the moment:
Coderbunker email registration.pdf
This creates an entry on Coderbunker Categorization:
https://docs.google.com/spreadsheets/d/11fWotgVODH5xuj6OH9YunbJCMnqCWD1Ud-pyJ_ID52A/edit#gid=0
Then we MANUALLY run a script
https://script.google.com/d/1Nnxm0mLzRMObgh_sO892JC05h5r_5eQTo3nXqDT_mbZoebpYLxTONPcB/edit
@Sunying-RONG read and integrate the script. I think what's interesting here is that you figured out a way to automatically run the mail sending script - that's the functionality we need to integrate to the next step.
This issue is seperated and finished by issues #67 #74 #75 #76
What changed and why?
When someone signs up on the website, we don't follow up with a thank you email or any email instructions for what to do next. Spend a lot of time to follow the onboarding process manually.
Background context or notes?
how to make a google app script
todo (if appropriate):
[ ] understand the onboarding process and summarize the steps. Referring to Onboarding Procedure for Freelancer or community manager.
[ ] create a form linked to a button of freelancer.coderbunker.com issue #67 to get necessary information (name, email address, objective ...) of people sign up.
[ ] store freelancers' information in a spreadsheet in coderbunker's drive.
[ ] using google script to send email to new signups, referring to existing google script
[ ] check how many new signups per month.
Goals
Reduce Coderbunker Onboarding freelancer process from 20 steps to 5 steps (-15 steps)
reviewer todo (if appropriate):