-
Notifications
You must be signed in to change notification settings - Fork 0
/
ClientReports
37 lines (33 loc) · 1.69 KB
/
ClientReports
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
/*
Sample script for automatically generating monthly client reports.
The example below retrieves the census total from any new client CSV files
uploaded into a specified Google folder, and then transfers the census total
into a custom client report template. After the client report is generated,
the original census file is stored in an archive and the client report is moved
into an outbox, ready for internal review before delivery to client.
The census total is presented as a sample data point. The script can be modified
to accommodate any additional data points included in the client report template.
*/
function prepareReports() {
var censusFolder = DriveApp.getFolderById("1TR0gwu4kNYApPDv0-B7AheBbf0NvYleV");
var censusFiles = censusFolder.getFiles();
while (censusFiles.hasNext()){
var nextFile = censusFiles.next();
var fileId = nextFile.getId();
var ss = SpreadsheetApp.openById(fileId);
var censusTotal = ss.getLastRow()-1;
var clientTemplate = DriveApp.getFileById("1zBpRoGz2qOanqsEDeR7K-A3zoTjwwIqyOkZu8Nk8S0Q");
var clientName = nextFile.getName();
var clientReport = clientTemplate.makeCopy(clientName + " Report");
var reportId = clientReport.getId();
var cr = SpreadsheetApp.openById(reportId);
cr.getRange('B2').setValue(censusTotal);
var outboxFolder = DriveApp.getFolderById("1MXdl8-1-R_vDY06iEwoGSXWk_9bNzpOu");
outboxFolder.addFile(clientReport);
var templateFolder = DriveApp.getFolderById("1Mr30IYWTs8ZAD_le7WBLpAt4tm-4YAtt");
templateFolder.removeFile(clientReport);
var archiveFolder = DriveApp.getFolderById("1qb6_Y7B8BAGrQzLAYm6xSpc2Xy7ufmaw");
archiveFolder.addFile(nextFile);
censusFolder.removeFile(nextFile);
}
}