Skip to main content

Create Spreadsheets from Data Rows in Google Sheets

You can use Document Studio to create spreadsheets and documents that display data from Google Sheets and Google Form responses. You can generate invoices, purchase orders or any other document that requires data from Google Sheets and makes use of Excel formulas and functions.

For this example, we have a Google Sheet that stores the invoice data of customers. The Document Studio add-on will help your business generate invoices from the data in the Google Sheet, one per row and the generated documents will be stored in the Google Drive of your Google Account.

Create Google Sheets Template​

As a first step, create a new spreadsheet template in Google Sheets. The merge fields are enclosed in double curly braces and they are used to display the data from the Google Sheet into the spreadsheet.

Google Sheets Invoice Template

Formulas and Functions​

You can use formulas in the spreadsheet template to calculate the values of the merge fields. For example, the cell F24 use the =SUM() function to calculate the total amount of the invoice.

The Total Price column is calculated using the PRODUCT() function of Google Sheets. The due date is calculated using by adding 'n' days to the D14 cell that contains the {{ Invoice Date }} taken from Google Sheets.

You can also embed QR codes, Google Drawings and images in the spreadsheet template as seen in the cell F3.

Prepare Data in Google Sheets​

Install Document Studio and open your Google Sheet to launch the add-on. You may either open an existing sheet or create a new sheet and add data manually to the sheet.

Create a new workflow, give a descriptive name and then click on Continue to move to the conditions page. On the Conditions pane, specify if you want your workflow to run only if the Google Sheet row satisfies certain conditions. By default, the workflow will run for all rows in the Google Sheet.

Google Sheets Data

Configure Invoice Generation​

Choose File from the list of available tasks. Next:

  • Choose the spreadsheet template that you've created in the previous step
  • Specify the Google Drive folder where the generated invoices would be saved
  • Specify the name of the generated invoice using dynamic placeholders
  • Choose the export format (PDF, Microsoft Excel or Google Sheets)
  • Specify if you want to password protect the generated invoice

Generate Invoice Spreadsheets

If you would like to send the generated spreadsheet invoice as an email attachment, turn on the option that says Attach file in email message. You would also have to create a new task for sending this email.

Click on the Preview button to see the generated files. Document Studio will take the data from the second row of the selected Google Sheet and generate a document in Drive. Click Done to locally apply your changes.

Click on Continue to proceed to the triggers screen. If you want the workflow to be automatically triggered every hour, so that any new rows added in that duration are processed automatically, you can choose the option Run workflow every hour and then click on the Save button.

The workflow will also appear in the workflow dashboard where you can edit, delete or manually run the workflow.

Sample Document​

Here's a sample PDF invoice spreadsheet generated from the Google Sheets template. All the dynamic formulas and placeholders are replaced with the data from the Google Sheet.

PDF Invoice Generated