Repository navigation
Expand file tree
/
Copy pathCode.js
More file actions
175 lines (148 loc) · 5 KB
/
Copy pathCode.js
File metadata and controls
175 lines (148 loc) · 5 KB
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
function onInstall(e) {
onOpen(e);
}
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('myMoney')
.addItem('Open myMoney', 'showSidebar')
.addToUi();
}
// Call this function to open the sidebar
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('sidebar.html')
.setTitle('myMoney')
.setWidth(300);
SpreadsheetApp.getUi().showSidebar(html);
}
function openPlaidModal() {
var plaid = getPlaidServiceInstance();
var link_token = plaid.createLinkToken();
var template = HtmlService.createTemplateFromFile('index');
template.linkToken = link_token;
var htmlOutput = template.evaluate().setWidth(450).setHeight(750);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Link Your Bank Account');
}
function linkBankAccount(public_token) {
var plaid = getPlaidServiceInstance();
plaid.getAccessToken(public_token);
// Sync transactions immediately after linking the bank account
var transactionsData = plaid.syncTransactions();
// Pass transactions array to writeToSheet function
var transactions = transactionsData.added;
writeToSheet(transactions);
// Writes the balance details to the balance history sheet
var allAccountDetails = plaid.syncAccountBalances();
writeBalanceHistoryToSheet(allAccountDetails);
}
function writeToSheet(transactions) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('Transactions');
if (!Array.isArray(transactions) || transactions.length === 0) {
SpreadsheetApp.getUi().alert('No new transactions to update.');
return;
}
// If the sheet doesn't exist, create it.
if (!sheet) {
sheet = spreadsheet.insertSheet('Transactions');
sheet.appendRow([
"Date",
"Name",
"Amount",
"Pending",
"Category",
"Account",
"Mask",
"Transaction ID",
"Pending Transaction ID",
]);
}
// Convert transactions to a 2D array
var data = transactions.map(function (transaction) {
return [
transaction.date,
transaction.name,
transaction.amount,
transaction.pending,
transaction.category[0],
transaction.account_name,
transaction.account_mask,
transaction.transaction_id,
transaction.pending_transaction_id,
];
});
// Append all transactions at once
sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
// Sort by date (column 1) in descending order
sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).sort({ column: 1, ascending: false });
}
function writeBalanceHistoryToSheet(accountDetailsArray) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('Balance History');
// If the sheet doesn't exist, create it.
if (!sheet) {
sheet = spreadsheet.insertSheet('Balance History');
sheet.appendRow([
"Date",
"Mask",
"Name",
"Official Name",
"Subtype",
"Available Balance",
"Current Balance",
"Limit"
]);
}
// Convert accountDetails to a 2D array
var currentDate = new Date();
var data = [];
for (var i = 0; i < accountDetailsArray.length; i++) {
var accountDetails = accountDetailsArray[i];
for (var accountId in accountDetails) {
var account = accountDetails[accountId];
data.push([
currentDate,
account.mask,
account.name,
account.official_name,
account.subtype,
account.available_balance,
account.subtype === "credit card" ? account.current_balance * -1 : account.current_balance,
account.limit,
]);
}
}
// Append all account data at once
sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
// Sort by date (column 1) in descending order
sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).sort({ column: 1, ascending: false });
}
function syncAndWriteTransactions() {
var plaid = getPlaidServiceInstance();
var transactionsData = plaid.syncTransactions();
// Pass transactions array to writeToSheet function
var transactions = transactionsData.added;
writeToSheet(transactions);
// Writes the balance details to the balance history sheet
var allAccountDetails = plaid.syncAccountBalances();
writeBalanceHistoryToSheet(allAccountDetails);
}
function fetchBankAccounts() {
var properties = PropertiesService.getScriptProperties().getProperties();
var bankAccounts = [];
for (var key in properties) {
if (key.startsWith('PLAID_ACCESS_TOKEN_')) {
var itemId = key.split('PLAID_ACCESS_TOKEN_')[1];
var officialNames = JSON.parse(properties['PLAID_OFFICIAL_NAMES_' + itemId]);
// Push an object with the item id and official names to the bankAccounts array
bankAccounts.push({
id: itemId,
names: officialNames
});
}
}
return bankAccounts;
}
function removeBankAccount(itemId) {
var plaidService = getPlaidServiceInstance();
return plaidService.deleteBankAccount(itemId);
}