FilesExpand file tree

 main

/

025_IntroToAppsScript_Project6.js

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

191 lines (151 loc) · 5.12 KB

 main

/

025_IntroToAppsScript_Project6.js

Copy path

Top

File metadata and controls

• Code
• Blame

191 lines (151 loc) · 5.12 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

/**

*

* Introduction To Apps Script

* Project: Coding Questionnaire

*

*/

// Project Lesson 1: Intro

// There is no code for this lesson

// Project Lesson 2: Setup Google Form & global variables

// switch for your own Google Form ID

// get from URL (X's in following example)

// https://docs.google.com/forms/d/XXXXXXXXXXXXXXXXXXXXXXXXX/edit

const FORM_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXX'; // <-- put your Form ID in here

// Project Lesson 3

// function to find the Form IDs

function findFormIDs() {

const form = FormApp.openById(FORM_ID);

const formItems = form.getItems();

formItems.forEach(item => console.log(item.getTitle() + ' ' + item.getId()));

}

/*

1:32:41 PM Notice Execution started

1:32:42 PM Info Name: 2120132702

1:32:42 PM Info Do you have prior coding experience? 267233812

1:32:42 PM Info What programming languages do you use? 1860180947

1:32:42 PM Notice Execution completed

*/

// Project Lesson 4: update the Google Form

function updateForm_v1() {

// get the language data from the Sheet

const ss = SpreadsheetApp.getActiveSpreadsheet();

const setupSheet = ss.getSheetByName('setup');

const langVals = setupSheet.getRange(2,1,setupSheet.getLastRow()-1,1).getValues();

console.log(langVals);

// [ [ 'None' ], [ 'Apps Script' ] ]

const langValsFlat = langVals.map(item => item[0]); // ['None'] => 'None'

console.log(langValsFlat);

// [ 'None', 'Apps Script' ] // flattened array

// add these languages to the form now

// get the form

const form = FormApp.openById(FORM_ID);

const langsCheckboxQuestion = form.getItemById('1860180947').asCheckboxItem();

// populate the Form checkbox question with new language data

langsCheckboxQuestion.setChoiceValues(langValsFlat);

}

// Project Lesson 5: Sending emails automatically with Apps Script

// version 1

function sendEmail_v1() {

// get spreadsheet

const ss = SpreadsheetApp.getActiveSpreadsheet();

const responseSheet = ss.getSheetByName('Form Responses 1');

const data = responseSheet.getDataRange().getValues();

console.log(data);

// remove the header row

data.shift();

console.log(data);

// loop over data

data.forEach((row,i) => {

// identify rows I haven't replied to yet

if (row[5] === '') {

// get email address

const email = row[2];

// write the email

const subject = 'Thank you for responding to the Apps Script questionnaire!';

let body = '';

// change the body for yes and no

// yes answer

if(row[3] === 'Yes') {

body = 'TBC - Yes answer';

}

// no answer

else {

body = 'TBC - No answer';

}

// send email

GmailApp.sendEmail(email,subject,body);

// mark as sent

const d = new Date();

responseSheet.getRange(i + 2,6).setValue(d);

}

else {

console.log('No email sent for this row');

}

});

}

// Project Lesson 6: Sending enhanced HTML emails automatically with Apps Script

// version 2

function sendEmail_v2() {

// get the spreadsheet information

const ss = SpreadsheetApp.getActiveSpreadsheet();

const responseSheet = ss.getSheetByName('Form Responses 1');

const data = responseSheet.getDataRange().getValues();

//console.log(data);

// remove the header

data.shift();

//console.log(data);

// loop over the rows

data.forEach((row,i) => {

// identify ones I haven't replied to

if (row[5] === '') {

// get the email address

const name = row[1];

const email = row[2];

const answer = row[3]; // yes/no

const langs = row[4]; // list of languages

// write the email

const subject = 'Thank you for responding to the Apps Script questionnaire!';

let body = '';

// change the body for yes and no

// yes answer

if (answer === 'Yes') {

body = 'Hi ' + name +`,<br><br>

Thank you for responding to our 2019 Developer Survey!<br><br>

Your feedback is greatly appreciated.<br><br>

You reported experience with the following coding languages:<br><br>

<em>` + langs + `</em><br><br>

Thanks,<br>

Ben`;

}

// no answer

else {

body = 'Hi ' + name +`,<br><br>

Thank you for responding to our 2019 Developer Survey!<br><br>

Your feedback is greatly appreciated.<br><br>

You reported not having any experience with coding, so here's a resource to get started:<br><br>

<a href="https://www.benlcollins.com/spreadsheets/starting-gas/">Getting started with Apps Script</a><br><br>

Thanks,<br>

Ben`;

}

//console.log(email);

//console.log(subject);

//console.log(body);

// send the email

GmailApp.sendEmail(email,subject,'',{htmlBody: body});

// mark as sent

const d = new Date();

responseSheet.getRange(i + 2,6).setValue(d);

}

else {

console.log('No email sent for this row');

}

});

}

// add a cutsom menu to our Sheet

function onOpen() {

const ui = SpreadsheetApp.getUi();

ui.createMenu("Questionnaire Menu")

.addItem("Update Form","updateForm_v1")

.addItem("Send Emails","sendEmail_v2")

.addToUi();

}