SuperSaaS: Log credit changes to Google Sheets
@guiprav
code:
data:privatelast 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 theauths 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

 
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.

 
Sheet1
string ·params.sheetName
SuperSaaS Event Body

Usually {{event.body}}. Use that if unsure.
Note: Don't forget to turn structured mode: off above.

 
key
 
value
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