-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathSpreadsheetToCsv.gs
39 lines (36 loc) · 1.11 KB
/
SpreadsheetToCsv.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
var settings = {
"key": '0Aprlzd4hAZrEdGVmYnBwZWdyVkUwQ0RBZE5BSXB1OEE',
"nl": '\n',
"sep": ','
};
function doGet() {
return ContentService.createTextOutput(convertSheetToCsv_(settings.key));
}
// We have two metacharacters: " and ,
// Iff the cell contains either of them, then its value is wrapped into quotes.
// Iff the cell contains quotes (prior to wrapping), then the quotes are escaped
// by doubling them.
function convertSheetToCsv_(id) {
var csv = "";
try {
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheets()[0];
var data = sheet.getDataRange().getValues();
for (var row = 0; row < data.length; row++) {
for (var col = 0; col < data[row].length; col++) {
var cell = data[row][col].toString();
cell = cell.replace(/"/g, '""');
cell = cell.replace(/\n/g, ' ');
if (cell.indexOf(settings.sep) != -1 || cell.indexOf('"') != -1) {
cell = '"' + cell + '"';
}
data[row][col] = cell;
}
csv += data[row].join(settings.sep) + settings.nl;
}
} catch(err) {
csv = err;
} finally {
return csv;
}
}