/
SendData.gs
207 lines (169 loc) · 6.38 KB
/
SendData.gs
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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
function sendData() {
const settingsSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const settingsSheet = settingsSpreadsheet.getSheetByName("Settings");
const formSheet = settingsSpreadsheet.getSheetByName("Form Questions");
const sourceSpreadsheetName = settingsSheet.getRange("B3").getValue();
const sourceWorksheetName = settingsSheet.getRange("B4").getValue();
const formURL = settingsSheet.getRange("B9").getValue();
var stopOnError = settingsSheet.getRange("B13").getValue();
var errors = "";
var dataSheet;
var headers;
var formFields;
var sectionsCounter;
// Retrive stored info about the form
try {
const scriptProperties = PropertiesService.getScriptProperties();
const savedFormURL = scriptProperties.getProperty('formURL');
if (savedFormURL != formURL) {
SpreadsheetApp.getUi().alert("Form URL not matching the fields. Try to analyze the form again");
return;
}
formFields = JSON.parse(scriptProperties.getProperty('formFields'));
headers = JSON.parse(scriptProperties.getProperty('headers'));
sectionsCounter = scriptProperties.getProperty('sectionsCounter');
} catch (err) {
// TODO (developer) - Handle exception
console.log('Failed with error %s', err.message);
}
// Check if parameters are empty
if (sourceSpreadsheetName == "" || sourceWorksheetName == "" || formURL == "") {
SpreadsheetApp.getUi().alert("Insert requested values: Spreadsheet url, Worksheet name, Form url");
return;
}
// Check if spreadsheet url is the same spreadsheet
if (sourceSpreadsheetName.split("d/")[1].split("/")[0] != SpreadsheetApp.getActiveSpreadsheet().getId()) {
try {
dataSheet = SpreadsheetApp.openByUrl(sourceSpreadsheetName).getSheetByName(sourceWorksheetName);
if (dataSheet == null) {
SpreadsheetApp.getUi().alert("Worksheet not valid");
return;
}
}
catch (e) {
SpreadsheetApp.getUi().alert("Spreadsheet url not valid");
return;
}
}
else {
dataSheet = settingsSpreadsheet.getSheetByName(sourceWorksheetName);
}
var formUrlSubmit;
var pageHistory = "?&pageHistory=0";
var splittedURL = formURL.split('/');
var formId = splittedURL[splittedURL.length - 2];
if (sectionsCounter == 1) {
formUrlSubmit = "https://docs.google.com/forms/u/0/d/e/" + formId + "/formResponse";
}
else {
for (let j = 1; j < sectionsCounter; j++) {
pageHistory = pageHistory + "," + j;
}
formUrlSubmit = "https://docs.google.com/forms/u/0/d/e/" + formId + "/formResponse" + pageHistory;
}
var startRow = settingsSheet.getRange("B6").getValue();
var endRow = settingsSheet.getRange("B7").getValue();
var lastRow = formSheet.getLastRow();
var valuesToUse = formSheet.getRange("B2:E" + lastRow).getValues();
// Contains the column index / custom value to use as input to the form
var dataToSend = [];
var header;
// Get the column index / custom value to use as input to the form
// if element of array is number it's a column reference on the source sheet, otherwise if string it is custom value
for (let i = 0; i < valuesToUse.length; i++) {
// If column and custom values are empty, we need to check if question is required by using stored array infos formFields
if (valuesToUse[i][0] == "" && valuesToUse[i][3] == "") {
if (formFields[i][3] == true) {
SpreadsheetApp.getUi().alert("Value is required for question: \n" + formFields[i][1] + " (Row " + (i + 2) + ")");
return;
}
else {
dataToSend.push("");
}
}
// Check if Custom value checkbox is checked
else if (valuesToUse[i][2] == true) {
dataToSend.push(valuesToUse[i][3].toString());
}
else {
// Find the dropdown column index in the source sheet
header = headers.indexOf(valuesToUse[i][0]);
if (header == -1) {
SpreadsheetApp.getUi().alert("Column name not found");
return;
}
dataToSend.push(header);
}
}
// String that contains all values to send
// I don't use an object because for checkbox you need multiple same key
var payload = "";
// Source data
var rowsToSubmit = dataSheet.getRange(startRow, 1, (endRow - startRow) + 1, dataSheet.getLastColumn()).getValues();
var questionType;
var entryid;
var answer;
var options;
// Itereate over the rows to submit
for (let x = 0; x < rowsToSubmit.length; x++) {
payload = "";
for (let i = 0; i < formFields.length; i++) {
questionType = formFields[i][0];
entryid = "entry." + formFields[i][2];
// If number it is a column index of the source sheet
if (typeof dataToSend[i] == "number") {
answer = rowsToSubmit[x][dataToSend[i]];
}
else {
answer = dataToSend[i];
}
// If column index to use or custom value is empty skip iteration
if (answer == "") {
continue;
}
// If checkbox question I need to repeat the same entry id for all selected checkboxes
if (questionType == 4) {
if (answer.search("\u2009") != -1) {
answer = answer.split("\u2009");
for (let j = 0; j < answer.length; j++) {
payload = payload + "&" + entryid + "=" + answer[j];
}
}
else {
payload = payload + "&" + entryid + "=" + answer;
}
}
else {
// If Date/Time question convert to date (for date I added also hour and minutes
// because in the form you can add the time to a date question)
if (questionType == 9) {
answer = Utilities.formatDate(new Date(answer), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), 'yyyy-MM-dd hh:mm');
}
if (questionType == 10) {
answer = Utilities.formatDate(new Date(answer), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), 'hh:mm:ss');
}
payload = payload + "&" + entryid + "=" + answer;
}
}
options = {
"method": "post",
"payload": payload
};
try {
UrlFetchApp.fetch(formUrlSubmit, options);
}
catch (e) {
if (stopOnError == "TRUE") {
SpreadsheetApp.getUi().alert("There was an error in row " + (startRow + x) + " in sheet " + sourceWorksheetName);
return;
}
else {
errors = errors + " Row " + (startRow + x);
}
}
}
if (errors != "") {
SpreadsheetApp.getUi().alert("There were some errors in rows: \n" + errors);
return;
}
}