-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathInvoice Generator.gs
199 lines (169 loc) · 9.63 KB
/
Invoice Generator.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
function runinvoicegenerator() {
var writecell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoice Generator').getRange("B20")
var cell = writecell.getValue();
if(cell == "Save and Email"){
writecell.setValue('Saving...')
generateinvoice();
writecell.setValue('Check your email');
};
}
function generateinvoice() {
var invoicesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoice Generator');
var infosheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information');
var farm = infosheet.getRange("B8").getValue()
var edit = invoicesheet.getRange('B19').getValue();
if (edit > 0){
var Invoicenum = invoicesheet.getRange('B19').getValue();
var invoicecount = invoicesheet.getRange('B4').getValue();
invoicesheet.getRange('B4').setValue(Invoicenum+"a");
var invid = infosheet.getRange('B56').getValue();
var manid = infosheet.getRange('B57').getValue();
DriveApp.getFileById(invid).setTrashed(true);
DriveApp.getFileById(manid).setTrashed(true);
}
var Spreadsheetid = SpreadsheetApp.getActiveSpreadsheet().getId()
var datevalue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoice Generator').getRange('B3').getValue()
var date = Utilities.formatDate(datevalue, "GMT-5", "MMM dd yyyy")
var infosheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information')
var packer = invoicesheet.getRange('B5').getValue()
var invoicesheetid = invoicesheet.getSheetId()
var url = "https://docs.google.com/spreadsheets/d/"+Spreadsheetid+"/export"+
"?format=pdf&"+
"size=7&"+
"fzr=true&"+
"portrait=true&"+
"fitw=true&"+
"gridlines=false&"+
"printtitle=false&"+
"sheetnames=false&"+
"pagenum=UNDEFINED&"+
"gid="+invoicesheetid+"&"+
"range=Invoice&"+
"ir=false&"+
"ic=false&";
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var Invoice = UrlFetchApp.fetch(url, params).getBlob().setName(farm+" "+packer+" Invoice "+date+".pdf");
var url = "https://docs.google.com/spreadsheets/d/"+Spreadsheetid+"/export"+
"?format=pdf&"+
"size=7&"+
"fzr=true&"+
"portrait=true&"+
"fitw=true&"+
"gridlines=false&"+
"printtitle=false&"+
"sheetnames=false&"+
"pagenum=UNDEFINED&"+
"attachment=true&"+
"gid="+invoicesheetid+"&"+
"range=Manifest&"+
"ir=false&"+
"ic=false&";
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var Manifest = UrlFetchApp.fetch(url, params).getBlob().setName(farm+" "+packer+" Manifest "+date+".pdf");
// save to drive, specify location
var driveid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information').getRange('B41').getValue()
var dir = DriveApp.getFolderById(driveid);
var idinvoice = dir.createFile(Invoice).getId();
invoicesheet.getRange('AG16').setValue(idinvoice);
var dir2 = DriveApp.getFolderById("1AUTovOVP1M6La5UT_-Fwhi2jVO5E68Gg")
var idinvoice2 = dir2.createFile(Invoice).getId()
invoicesheet.getRange('AI16').setValue(idinvoice2)
var idmanifest = dir.createFile(Manifest).getId();
invoicesheet.getRange('AH16').setValue(idmanifest);
DriveApp.getFolderById(driveid)
//or send as email
var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information').getRange('B14').getValue()
var body = "Here you go!"
var subject = packer+" Invoice and Manifest "+date
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments:[Invoice, Manifest]
});
/*
//attach to group email
var gmaildraftid = infosheet.getRange("B59").getValue();
var draftto = infosheet.getRange("B60").getValue();
var draftsub = infosheet.getRange("B62").getValue()+" "+date;
var draftbody = infosheet.getRange("B61").getValue();
var idrow = infosheet.getRange("B63").getValue();
if (gmaildraftid == ""){
var gmaildraftid = GmailApp.createDraft(draftto, draftsub, draftbody, {
htmlBody: body,
attachments:[Invoice]
}).getId();
SpreadsheetApp.openById("1G3Ygmv8FxKYtyr39_pP9i1Cv0kGaAO8aTgvx5X_RH28").getSheetByName("Code Variables").getRange(idrow).setValue(gmaildraftid);
}//end if
else {
GmailApp.getDraft(gmaildraftid).update(draftto, draftsub, draftbody, {
htmlBody: body,
attachments:[Invoice]
});
};//end else
*/
// save invoice information to the shipping records sheet
var copy = infosheet.getRange('B52').getValue();
var records = invoicesheet.getRange(copy).getValues();
var addrow = infosheet.getRange('B54').getValue();
var paste = infosheet.getRange('B53').getValue();
if (edit == "Create New Invoice"){
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Shipping Records').insertRowAfter(addrow);
}
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Shipping Records').getRange(paste).setValues(records);
invoicesheet.getRange('B19').setValue("Create New Invoice")
//updates invoice number each time script is run
if (edit == "Create New Invoice"){
var Invoicenum = invoicesheet.getRange('B4').getValue();
invoicesheet.getRange('B4').setValue(Invoicenum+1);
}
else {
invoicesheet.getRange('B4').setValue(invoicecount)
}
}
function runcompanimal() {
var writecell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Compromised Animal Form').getRange("B4")
var cell = writecell.getValue();
if(cell == "Save and Email"){
writecell.setValue('Saving...')
companimal();
writecell.setValue('Check your email');
};
}
function companimal() {
var companimalsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Compromised Animal Form');
var infosheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information');
var farm = infosheet.getRange("B8").getValue()
var invoicesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoice Generator');
var Spreadsheetid = SpreadsheetApp.getActiveSpreadsheet().getId()
var datevalue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoice Generator').getRange('B3').getValue()
var date = Utilities.formatDate(datevalue, "GMT-5", "MMM dd yyyy")
var packer = invoicesheet.getRange('B5').getValue()
var companimalsheetid = companimalsheet.getSheetId()
var url = "https://docs.google.com/spreadsheets/d/"+Spreadsheetid+"/export"+
"?format=pdf&"+
"size=7&"+
"fzr=true&"+
"portrait=true&"+
"fitw=true&"+
"gridlines=false&"+
"printtitle=false&"+
"sheetnames=false&"+
"pagenum=UNDEFINED&"+
"gid="+companimalsheetid+"&"+
"range=Companimalform&"+
"ir=false&"+
"ic=false&";
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var companimalform = UrlFetchApp.fetch(url, params).getBlob().setName(farm+" "+packer+" Compromised Animal Form "+date+".pdf");
// save to drive, specify location
var driveid = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information').getRange('B41').getValue()
var dir = DriveApp.getFolderById(driveid);
dir.createFile(companimalform)
//or send as email
var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Producer Information').getRange('B14').getValue()
var body = "Here you go!"
var subject = packer+" Compromised Animal Form "+date
GmailApp.sendEmail(email, subject, body, {
htmlBody: body,
attachments:[companimalform]
});
}