SuperSaaS: Log credit changes to Google Sheets
•@guiprav2•
•code:
•data:private•last updated:4 years ago
today
Build integrations remarkably fast!
You're viewing a public workflow template.
Sign up to customize, add steps, modify code and more.
Join 1,000,000+ developers using the Pipedream platform
steps.
trigger
active
•last updated:-•last event:-steps.
log_credit_changes_to_google_sheet
auth
(auths.google_sheets)
params
Google Sheet ID
Copy the target sheet ID from your Google Sheet URL where you want to log data to.
E.g.: https://docs.google.com/spreadsheets/d/**EXAMPLE_ID**/edit#gid=0 => EXAMPLE_ID
string ·params.sheetId
Sheet Name
Type Sheet1 if unsure.
Named sheets are tabs at the bottom of your spreadsheet, not your document name.
This field is not related to your document name.
string ·params.sheetName
SuperSaaS Event Body
Usually {{event.body}}. Use that if unsure.
Note: Don't forget to turn structured mode: off above.
object ·params.eventBody
code
async (params, auths) => {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
// NOTE: These declarations, including their declaration order,
// map to and affect the fields above.
const { sheetId } = params;
const sheetName = params.sheetName;
const evBody = params.eventBody;
const dayjs = require('dayjs');
const isoToSheetDate = x => dayjs(x).format('YYYY-MM-DD HH:mm:ss');
const centsToDecimal = x => (Number(x) / 100).toFixed(2);
const localeToDecimal = x => (Number(String(x).replace(/\D/g, '')) / 100).toFixed(2);
const headerCols = ['Date/Time', 'E-mail', 'Event', 'Credit'];
const evCols = [isoToSheetDate(evBody.created_on), evBody.email];
switch (evBody.event) {
case 'new':
evCols.push('Account creation (starting credit)', localeToDecimal(evBody.credit));
break;
case 'change':
evCols.push('Account update (credit reset)', localeToDecimal(evBody.credit));
break;
case 'purchase':
evCols.push(
`Credit purchase (price: $${centsToDecimal(evBody.price)})`,
centsToDecimal(evBody.credit),
);
break;
case 'create':
evCols.push(
`Scheduled an appointment for ${isoToSheetDate(evBody.start)}`,
centsToDecimal(-Number(evBody.price)),
);
break;
case 'destroy':
case 'edit':
if (!evBody.deleted) {
return;
}
evCols.push(
`Deleted an appointment for ${isoToSheetDate(evBody.start)}`,
centsToDecimal(evBody.price),
);
break;
default:
// Ignore unrelated events.
return;
}
const firstRun = this.$checkpoint ? this.$checkpoint.firstRun : true;
if (firstRun) {
this.$checkpoint = { firstRun: false };
}
const config = {
method: "post",
url: `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${sheetName}:append`,
params: {
includeValuesInResponse: true,
valueInputOption: "USER_ENTERED"
},
headers: {
Authorization: `Bearer ${auths.google_sheets.oauth_access_token}`,
},
data: { values: firstRun ? [headerCols, evCols] : [evCols] },
}
return (await require("@pipedreamhq/platform").axios(this, config)).updates