auths
objectCopy 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
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.
Usually {{event.body}}
. Use that if unsure.
Note: Don't forget to turn structured mode: off above.
return
or this.key = 'value'
, pass input data to your code viaparams
, and maintain state across executions with$checkpoint.async
(event, steps, params, auths) => {
}
// 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