-
Notifications
You must be signed in to change notification settings - Fork 0
/
g-script
47 lines (45 loc) · 1.45 KB
/
g-script
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
function doGet(e){
var keyVals = parseValues(e);
addRow(keyVals);
return ContentService.createTextOutput('Success!');
}
function parseValues(e){
var rawKeyVals = JSON.stringify(e.parameter).split('"');
var parsedKeys = [];
var parsedVals = [];
for(i=3;i<rawKeyVals.length;i=i+4){
parsedVals.push(rawKeyVals[i]);
}
for(i=1;i<rawKeyVals.length;i=i+4){
parsedKeys.push(rawKeyVals[i]);
}
var keyVals = [parsedKeys, parsedVals];
return keyVals;
}
function addRow(keyVals){
var keys = keyVals[0];
var vals = keyVals[1];
var ss = SpreadsheetApp.openByUrl("**YOUR SPREADSHEETS URL GOES HERE**");
var sheets = ss.getSheets();
var sheet = sheets[0];
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
//get column labels (row 1 values)
var headerVals = sheet.getRange(1,1,1,lastCol).getValues();
//create correct length array for new row values by cloning headerVals[]
var newRowVals = headerVals[0].slice(0);
//add new values to newRowVals. add 'no data' if no data
for(i=0;i<headerVals[0].length;i++){
var index = keys.indexOf(headerVals[0][i]);
if(index >= 0){
newRowVals[i] = vals[index];
}else{
newRowVals[i] = 'no data';
}
}
//add new (blank) row to spreadsheet after the header row, then populate row with new data
sheet.insertRowAfter(1);
var insertRange = sheet.getRange(2,1,1,newRowVals.length);
var insertValues = [newRowVals];
insertRange.setValues(insertValues);
}