Create Invoice with Zoho Books API on New Column from PostgreSQL API

Pipedream makes it easy to connect APIs for Zoho Books, PostgreSQL and 2,400+ other apps remarkably fast.

Trigger workflow on

New Column from the PostgreSQL API

Next, do this

Create Invoice with the Zoho Books API

No credit card required

▶

Watch us build a workflow

8 min

Watch now ➜

Trusted by 1,000,000+ developers from startups to Fortune 500 companies

Developers ♥ Pipedream

Getting Started#

This integration creates a workflow with a PostgreSQL trigger and Zoho Books action. When you configure and deploy the workflow, it will run on Pipedream's servers 24x7 for free.

Select this integration
Configure the New Column trigger
1. Connect your PostgreSQL account
2. Configure Polling Interval
3. Select a Schema
4. Select a Table
Configure the Create Invoice action
1. Connect your Zoho Books account
2. Select a Customer Id
3. Optional- Configure Invoice Number
4. Optional- Select a Place Of Supply
5. Optional- Configure VAT Treatment
6. Optional- Configure Reference Number
7. Optional- Select a Template Id
8. Optional- Configure Date
9. Optional- Configure Payment Terms
10. Optional- Configure Payment Terms Label
11. Optional- Configure Due Date
12. Optional- Configure Discount
13. Optional- Configure Is Discount Before Tax
14. Optional- Select a Discount Type
15. Optional- Configure Is Inclusive Tax
16. Optional- Configure Exchange Rate
17. Optional- Select a Recurring Invoice Id
18. Optional- Configure Invoiced Estimate Id
19. Optional- Configure Salesperson Name
20. Optional- Configure Custom Fields
21. Optional- Configure Line Items
22. Optional- Configure Payment Options
23. Optional- Configure Allow Partial Payments
24. Optional- Configure Custom Body
25. Optional- Configure Custom Subject
26. Optional- Configure Notes
27. Optional- Configure Terms
28. Optional- Configure Shipping Charge
29. Optional- Configure Adjustment
30. Optional- Configure Adjustment Description
31. Optional- Configure Reason
32. Optional- Select a Tax Authority Id
33. Optional- Select a Tax Exemption Id
34. Optional- Select a Tax Id
35. Optional- Select a Expense Id
36. Optional- Configure Salesorder Item Id
37. Optional- Configure Avatax Tax Code
38. Optional- Select one or more Time Entry Ids
39. Optional- Configure Send
40. Optional- Configure Ignore Auto Number Generation
Deploy the workflow
Send a test event to validate your setup
Turn on the trigger

Details#

This integration uses pre-built, source-available components from Pipedream's GitHub repo. These components are developed by Pipedream and the community, and verified and maintained by Pipedream.

To contribute an update to an existing component or create a new component, create a PR on GitHub. If you're new to Pipedream component development, you can start with quickstarts for trigger span and action development, and then review the component API reference.

Trigger#

New Column on PostgreSQL

Description:Emit new event when a new column is added to a table. [See the documentation](https://node-postgres.com/features/queries)

Version:2.0.6

Key:postgresql-new-column

View on GitHub

PostgreSQL Overview#

On Pipedream, you can leverage the PostgreSQL app to create workflows that automate database operations, synchronize data across platforms, and react to database events in real-time. Think handling new row entries, updating records from webhooks, or even compiling reports on a set schedule. Pipedream's serverless platform provides a powerful way to connect PostgreSQL with a variety of apps, enabling you to create tailored automation that fits your specific needs.

Trigger Code#

import common from "../common.mjs";

export default {
  ...common,
  name: "New Column",
  key: "postgresql-new-column",
  description: "Emit new event when a new column is added to a table. [See the documentation](https://node-postgres.com/features/queries)",
  version: "2.0.6",
  type: "source",
  props: {
    ...common.props,
    schema: {
      propDefinition: [
        common.props.postgresql,
        "schema",
      ],
    },
    table: {
      propDefinition: [
        common.props.postgresql,
        "table",
        (c) => ({
          schema: c.schema,
        }),
      ],
    },
  },
  async run() {
    const previousColumns = this._getPreviousValues() || [];

    const columns = await this.postgresql.getColumns(this.table, this.schema);

    const newColumns = columns.filter((column) => !previousColumns.includes(column));
    for (const column of newColumns) {
      const meta = this.generateMeta(column);
      this.$emit(column, meta);
    }

    this._setPreviousValues(columns);
  },
};

Trigger Configuration#

This component may be configured based on the props defined in the component code. Pipedream automatically prompts for input values in the UI and CLI.

Label	Prop	Type	Description
PostgreSQL	`postgresql`	`app`	This component uses the PostgreSQL app.
N/A	`db`	`$.service.db`	This component uses `$.service.db` to maintain state between executions.
Polling Interval	`timer`	`$.interface.timer`	Pipedream will poll the API on this schedule
Schema	`schema`	`string`	Select a value from the drop down menu.
Table	`table`	`string`	Select a value from the drop down menu.

Trigger Authentication#

PostgreSQL uses API keys for authentication. When you connect your PostgreSQL account, Pipedream securely stores the keys so you can easily authenticate to PostgreSQL APIs in both code and no-code steps.

Connecting to Restricted Databases

Either enable the shared static IP for this account below, or configure a VPC to deploy any workflow in your workspace to a private network with a dedicated static IP. Learn more in our docs.

SSL Setup

Configure SSL on your PostgreSQL database by providing the CA (Certificate Authority), and choosing between Full Verification, Verify Certificate Authority (CA), or Skip Verification. Skipping verification is not recommended as this has serious security implications.

About PostgreSQL#

PostgreSQL is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Action#

Create Invoice on Zoho Books

Description:Creates an invoice for your customer. [See the documentation](https://www.zoho.com/books/api/v3/invoices/#create-an-invoice)

Version:0.3.0

Key:zoho_books-create-invoice

View on GitHub

Zoho Books Overview#

Zoho Books API unlocks the potential to automate and streamline accounting tasks by integrating with Pipedream's serverless platform. With this powerful combo, you can automate invoicing, manage your accounts, reconcile bank transactions, and handle contacts and items without manual input. By setting up event-driven workflows, you can ensure data consistency across platforms, trigger notifications, and generate reports, all while saving time and reducing human error.

Action Code#

// legacy_hash_id: a_EVix1V
import { parseObject } from "../../common/utils.mjs";
import zohoBooks from "../../zoho_books.app.mjs";

export default {
  key: "zoho_books-create-invoice",
  name: "Create Invoice",
  description: "Creates an invoice for your customer. [See the documentation](https://www.zoho.com/books/api/v3/invoices/#create-an-invoice)",
  version: "0.3.0",
  type: "action",
  props: {
    zohoBooks,
    customerId: {
      propDefinition: [
        zohoBooks,
        "customerId",
      ],
      description: "ID of the customer the invoice has to be created.",
    },
    invoiceNumber: {
      type: "string",
      label: "Invoice Number",
      description: "Search invoices by invoice number. Max-length [100]",
      optional: true,
    },
    placeOfSupply: {
      propDefinition: [
        zohoBooks,
        "placeOfSupply",
      ],
      optional: true,
    },
    vatTreatment: {
      propDefinition: [
        zohoBooks,
        "vatTreatment",
      ],
      optional: true,
    },
    referenceNumber: {
      type: "string",
      label: "Reference Number",
      description: "The reference number of the invoice.",
      optional: true,
    },
    templateId: {
      propDefinition: [
        zohoBooks,
        "templateId",
      ],
      optional: true,
    },
    date: {
      type: "string",
      label: "Date",
      description: "Search invoices by invoice date. Default date format is yyyy-mm-dd.",
      optional: true,
    },
    paymentTerms: {
      propDefinition: [
        zohoBooks,
        "paymentTerms",
      ],
      optional: true,
    },
    paymentTermsLabel: {
      propDefinition: [
        zohoBooks,
        "paymentTermsLabel",
      ],
      optional: true,
    },
    dueDate: {
      type: "string",
      label: "Due Date",
      description: "Search invoices by due date. Default date format is yyyy-mm-dd. `Variants: due_date_start, due_date_end, due_date_before and due_date_after`",
      optional: true,
    },
    discount: {
      propDefinition: [
        zohoBooks,
        "discount",
      ],
      optional: true,
      description: "Discount applied to the invoice. It can be either in % or in amount. e.g. 12.5% or 190. Max-length [100]",
    },
    isDiscountBeforeTax: {
      propDefinition: [
        zohoBooks,
        "isDiscountBeforeTax",
      ],
      optional: true,
    },
    discountType: {
      propDefinition: [
        zohoBooks,
        "discountType",
      ],
      optional: true,
    },
    isInclusiveTax: {
      propDefinition: [
        zohoBooks,
        "isInclusiveTax",
      ],
      optional: true,
    },
    exchangeRate: {
      type: "string",
      label: "Exchange Rate",
      description: "Exchange rate of the currency.",
      optional: true,
    },
    recurringInvoiceId: {
      propDefinition: [
        zohoBooks,
        "recurringInvoiceId",
        ({ customerId }) => ({
          customerId,
        }),
      ],
      optional: true,
    },
    invoicedEstimateId: {
      type: "string",
      label: "Invoiced Estimate Id",
      description: "ID of the invoice from which the invoice is created.",
      optional: true,
    },
    salespersonName: {
      propDefinition: [
        zohoBooks,
        "salespersonName",
      ],
      optional: true,
    },
    customFields: {
      propDefinition: [
        zohoBooks,
        "customFields",
      ],
      description: "A list of custom fields objects for an invoice. **Example: {\"customfield_id\": 123123, \"value\": \"value\"}**",
      optional: true,
    },
    lineItems: {
      propDefinition: [
        zohoBooks,
        "lineItems",
      ],
      optional: true,
      description: "A list of line items objects of an estimate. **Example: {\"item_id\": \"1352827000000156060\", \"notes\": \"note\", \"name\": \"Item name\", \"quantity\": \"1\" }** [See the documentation](https://www.zoho.com/books/api/v3/invoices/#create-an-invoice) for further details.",
    },
    paymentOptions: {
      type: "object",
      label: "Payment Options",
      description: "Payment options for the invoice, online payment gateways and bank accounts. Will be displayed in the pdf. **Example: {\"payment_gateways\": [\"configured\": true, \"additional_field1\": \"standard\", \"gateway_name\": \"paypal\"]}** [See the documentation](https://www.zoho.com/books/api/v3/invoices/#create-an-invoice) for further details.",
      optional: true,
    },
    allowPartialPayments: {
      type: "boolean",
      label: "Allow Partial Payments",
      description: "Boolean to check if partial payments are allowed for the contact",
      optional: true,
    },
    customBody: {
      propDefinition: [
        zohoBooks,
        "customBody",
      ],
      optional: true,
    },
    customSubject: {
      propDefinition: [
        zohoBooks,
        "customSubject",
      ],
      optional: true,
    },
    notes: {
      propDefinition: [
        zohoBooks,
        "notes",
      ],
      description: "The notes added below expressing gratitude or for conveying some information.",
      optional: true,
    },
    terms: {
      propDefinition: [
        zohoBooks,
        "terms",
      ],
      optional: true,
    },
    shippingCharge: {
      propDefinition: [
        zohoBooks,
        "shippingCharge",
      ],
      optional: true,
    },
    adjustment: {
      propDefinition: [
        zohoBooks,
        "adjustment",
      ],
      optional: true,
    },
    adjustmentDescription: {
      propDefinition: [
        zohoBooks,
        "adjustmentDescription",
      ],
      optional: true,
    },
    reason: {
      type: "string",
      label: "Reason",
      optional: true,
    },
    taxAuthorityId: {
      propDefinition: [
        zohoBooks,
        "taxAuthorityId",
      ],
      optional: true,
    },
    taxExemptionId: {
      propDefinition: [
        zohoBooks,
        "taxExemptionId",
      ],
      optional: true,
    },
    taxId: {
      propDefinition: [
        zohoBooks,
        "taxId",
      ],
      optional: true,
    },
    expenseId: {
      propDefinition: [
        zohoBooks,
        "expenseId",
      ],
      optional: true,
    },
    salesorderItemId: {
      type: "string",
      label: "Salesorder Item Id",
      description: "ID of the sales order line item which is invoices.",
      optional: true,
    },
    avataxTaxCode: {
      type: "string",
      label: "Avatax Tax Code",
      description: "A tax code is a unique label used to group Items (products, services, or charges) together. Refer the [link][2] for more deails. Max-length [25]",
      optional: true,
    },
    timeEntryIds: {
      propDefinition: [
        zohoBooks,
        "timeEntryIds",
      ],
      optional: true,
    },
    send: {
      propDefinition: [
        zohoBooks,
        "send",
      ],
      optional: true,
    },
    ignoreAutoNumberGeneration: {
      propDefinition: [
        zohoBooks,
        "ignoreAutoNumberGeneration",
      ],
      optional: true,
      description: "Ignore auto estimate number generation for this estimate. This mandates the estimate number.",
    },
  },
  async run({ $ }) {
    const response = await this.zohoBooks.createInvoice({
      $,
      params: {
        send: this.send,
        ignore_auto_number_generation: this.ignoreAutoNumberGeneration,
      },
      data: {
        customer_id: this.customerId,
        invoice_number: this.invoiceNumber,
        place_of_supply: this.placeOfSupply,
        vat_treatment: this.vatTreatment,
        reference_number: this.referenceNumber,
        template_id: this.templateId,
        date: this.date,
        payment_terms: this.paymentTerms,
        payment_terms_label: this.paymentTermsLabel,
        due_date: this.dueDate,
        discount: this.discount && parseFloat(this.discount),
        is_discount_before_tax: this.isDiscountBeforeTax,
        discount_type: this.discountType,
        is_inclusive_tax: this.isInclusiveTax,
        exchange_rate: this.exchangeRate && parseFloat(this.exchangeRate),
        recurring_invoice_id: this.recurringInvoiceId,
        invoiced_estimate_id: this.invoicedEstimateId,
        salesperson_name: this.salespersonName,
        custom_fields: parseObject(this.customFields),
        line_items: parseObject(this.lineItems),
        payment_options: parseObject(this.paymentOptions),
        allow_partial_payments: this.allowPartialPayments,
        custom_body: this.customBody,
        custom_subject: this.customSubject,
        notes: this.notes,
        terms: this.terms,
        shipping_charge: this.shippingCharge && parseFloat(this.shippingCharge),
        adjustment: this.adjustment && parseFloat(this.adjustment),
        adjustment_description: this.adjustmentDescription,
        reason: this.reason,
        tax_authority_id: this.taxAuthorityId,
        tax_exemption_id: this.taxExemptionId,
        tax_id: this.taxId,
        expense_id: this.expenseId,
        salesorder_item_id: this.salesorderItemId,
        avatax_tax_code: this.avataxTaxCode,
        time_entry_ids: parseObject(this.timeEntryIds),
      },
    });

    $.export("$summary", `Invoice successfully created with Id: ${response.invoice.invoice_id}`);
    return response;
  },
};

Action Configuration#

This component may be configured based on the props defined in the component code. Pipedream automatically prompts for input values in the UI.

Label	Prop	Type	Description
Zoho Books	`zohoBooks`	`app`	This component uses the Zoho Books app.
Customer Id	`customerId`	`string`	Select a value from the drop down menu.
Invoice Number	`invoiceNumber`	`string`	Search invoices by invoice number. Max-length [100]
Place Of Supply	`placeOfSupply`	`string`	Select a value from the drop down menu:`{ "label": "Abu Dhabi (UAE Supported)", "value": "AB" }{ "label": "Ajman (UAE Supported)", "value": "AJ" }{ "label": "Dubai (UAE Supported)", "value": "DU" }{ "label": "Fujairah (UAE Supported)", "value": "FU" }{ "label": "Ras al-Khaimah (UAE Supported)", "value": "RA" }{ "label": "Sharjah (UAE Supported)", "value": "SH" }{ "label": "Umm al-Quwain (UAE Supported)", "value": "UM" }{ "label": "United Arab Emirates (GCC Supported)", "value": "AE" }{ "label": "Saudi Arabia (GCC Supported)", "value": "SA" }{ "label": "Bahrain (GCC Supported)", "value": "BH" }{ "label": "Kuwait (GCC Supported)", "value": "KW" }{ "label": "Oman (GCC Supported)", "value": "OM" }{ "label": "Qatar (GCC Supported)", "value": "QA" }`
VAT Treatment	`vatTreatment`	`string`	Enter vat treatment.
Reference Number	`referenceNumber`	`string`	The reference number of the invoice.
Template Id	`templateId`	`string`	Select a value from the drop down menu.
Date	`date`	`string`	Search invoices by invoice date. Default date format is yyyy-mm-dd.
Payment Terms	`paymentTerms`	`integer`	Payment terms in days e.g. 15, 30, 60. Invoice due date will be calculated based on this. Max-length [100]
Payment Terms Label	`paymentTermsLabel`	`string`	Used to override the default payment terms label. Default value for 15 days is "Net 15 Days". Max-length [100]
Due Date	`dueDate`	`string`	Search invoices by due date. Default date format is yyyy-mm-dd. `Variants: due_date_start, due_date_end, due_date_before and due_date_after`
Discount	`discount`	`string`	Discount applied to the invoice. It can be either in % or in amount. e.g. 12.5% or 190. Max-length [100]
Is Discount Before Tax	`isDiscountBeforeTax`	`boolean`	Used to specify how the discount has to applied. Either before or after the calculation of tax.
Discount Type	`discountType`	`string`	Select a value from the drop down menu:`entity_levelitem_level`
Is Inclusive Tax	`isInclusiveTax`	`boolean`	Used to specify whether the line item rates are inclusive or exclusive of tax.
Exchange Rate	`exchangeRate`	`string`	Exchange rate of the currency.
Recurring Invoice Id	`recurringInvoiceId`	`string`	Select a value from the drop down menu.
Invoiced Estimate Id	`invoicedEstimateId`	`string`	ID of the invoice from which the invoice is created.
Salesperson Name	`salespersonName`	`string`	Name of the sales person.
Custom Fields	`customFields`	`string[]`	A list of custom fields objects for an invoice. Example: {"customfield_id": 123123, "value": "value"}
Line Items	`lineItems`	`string[]`	A list of line items objects of an estimate. Example: {"item_id": "1352827000000156060", "notes": "note", "name": "Item name", "quantity": "1" } See the documentation for further details.
Payment Options	`paymentOptions`	`object`	Payment options for the invoice, online payment gateways and bank accounts. Will be displayed in the pdf. Example: {"payment_gateways": ["configured": true, "additional_field1": "standard", "gateway_name": "paypal"]} See the documentation for further details.
Allow Partial Payments	`allowPartialPayments`	`boolean`	Boolean to check if partial payments are allowed for the contact
Custom Body	`customBody`	`string`
Custom Subject	`customSubject`	`string`
Notes	`notes`	`string`	The notes added below expressing gratitude or for conveying some information.
Terms	`terms`	`string`	The terms added below expressing gratitude or for conveying some information.
Shipping Charge	`shippingCharge`	`string`	Shipping charges applied to the invoice.
Adjustment	`adjustment`	`string`
Adjustment Description	`adjustmentDescription`	`string`
Reason	`reason`	`string`
Tax Authority Id	`taxAuthorityId`	`string`	Select a value from the drop down menu.
Tax Exemption Id	`taxExemptionId`	`string`	Select a value from the drop down menu.
Tax Id	`taxId`	`string`	Select a value from the drop down menu.
Expense Id	`expenseId`	`string`	Select a value from the drop down menu.
Salesorder Item Id	`salesorderItemId`	`string`	ID of the sales order line item which is invoices.
Avatax Tax Code	`avataxTaxCode`	`string`	A tax code is a unique label used to group Items (products, services, or charges) together. Refer the [link][2] for more deails. Max-length [25]
Time Entry Ids	`timeEntryIds`	`string[]`	Select a value from the drop down menu.
Send	`send`	`boolean`	Send the estimate to the contact person(s) associated with the estimate.
Ignore Auto Number Generation	`ignoreAutoNumberGeneration`	`boolean`	Ignore auto estimate number generation for this estimate. This mandates the estimate number.

Action Authentication#

Zoho Books uses OAuth authentication. When you connect your Zoho Books account, Pipedream will open a popup window where you can sign into Zoho Books and grant Pipedream permission to connect to your account. Pipedream securely stores and automatically refreshes the OAuth tokens so you can easily authenticate any Zoho Books API.

Pipedream requests the following authorization scopes when you connect your account:

ZohoBooks.fullaccess.all