-
Notifications
You must be signed in to change notification settings - Fork 0
/
formToSheet.js
65 lines (56 loc) · 1.93 KB
/
formToSheet.js
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
// Run this first before making a trigger to get the Form permissions
function getForm() {
FormApp.getActiveForm()
}
function getFormData(e) {
const formResponses = e.response.getItemResponses();
const dict = {};
// Optional email
// let email = e.response.getRespondentEmail();
// get responses
formResponses.forEach(formResponse => {
dict[formResponses.getItem().getTitle()] = formResponse.getResponse();
})
// Clean the data
dict["First Name"] = dict["First Name"] ? dict["First Name"].trim(): dict["First Name"];
dict["Last Name"] = dict["Last Name"] ? dict["Last Name"].trim() : dict["Last Name"];
dict["Phone"] = dict["Phone"] ? dict["Phone"].trim() : dict["Phone"];
addRowToDb(dict);
}
function openSS() {
return SpreadsheetApp.openByUrl(databaseSSURL);
}
function findTabName(param) {
// Optional find tab by param
// const re = new RegExp(tabName);
// if(re.test( sheets[j].getName())) {
// // Found tab add data
// let sheet = ss.getSheetByName(sheets[j].getName());
// try {
// let insertAtRow = sheet.getLastRow() + 1;
// let data = [dict["Last Name"], dict["First Name"], dict["Phone"], "", "", "", "", dict["Points"], dict["Phase"]];
// sheet.getRange(insertAtRow, 1, 1, data.length).setValues([data]);
// SpreadsheetApp.flush();
// }
// catch(error) {
// console.error(error)
// }
// }
// console.error("Could not find matching tab for " + tabName)
return false;
}
function addRowToDb(dict) {
const ss = SpreadsheetApp.openByUrl(databaseSSURL);
const sheets = ss.getSheets();
const tabName = findTabName(param) || "defaultTab";
const sheet = ss.getSheetByName(tabName);
try {
let insertAtRow = sheet.getLastRow() + 1;
let data = [dict["Last Name"], dict["First Name"], dict["Phone"]];
sheet.getRange(insertAtRow, 1, 1, data.length).setValues([data]);
SpreadsheetApp.flush();
}
catch(error) {
console.error(error)
}
}