SuperSaaS: Log credit changes to Google Sheets
•@guiprav•
•code:
•data:private•last updated:3 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
inactive
•last updated:-•last event:-steps.
log_credit_changes_to_google_sheet
auth
to use OAuth tokens and API keys in code via the
auths object(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
Write any Node.jscodeand use anynpm package. You can alsoexport datafor use in later steps via
return or this.key = 'value', pass input data to your code viaparams, and maintain state across executions with$checkpoint.async (event, steps, 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
80
81
82
}83
// 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 => {
const digitsOnly = Number(String(x).replace(/\D/g, ''));
return /\d[.,]\d\d$/.test(x) ? (digitsOnly / 100).toFixed(2) : digitsOnly.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.credit)),
);
break;
case 'destroy':
case 'edit':
if (!evBody.deleted) {
return;
}
evCols.push(
`Deleted an appointment for ${isoToSheetDate(evBody.start)}`,
centsToDecimal(evBody.credit),
);
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