(Example) Configure an Excel Write Activity

This example shows how to configure an Excel Write activity to change the values of cells in a Microsoft Excel file based on data entered in an eForm.

Background and Setup

Result

This example shows how to add and configure an Excel Write activity in the Sales Invoice app to change the values of cells in a Microsoft Excel file based on data entered in an eForm.

When you add the Sales Invoice app from the App Store to the App Builder, and open the process, it looks like this:

Sales Invoice Process Model Preview
When you complete this example procedure, the finished process looks like this:

Sales Invoice Process Model Final

When you create the Microsoft Excel file, it looks like this:


Microsoft Excel Template
When you start the process and submit the Sales Invoice Request form, the completed form looks like this:

Sales Invoice Request Form
The data from the fields in the Requester Details section of the Sales Invoice Request form show in the Sales Invoice.xlsx Microsoft Excel file.

Sales Invoive Finance Manager Approval eForm

Prerequisites

  • This example uses the Sales Invoice app from the AgilePoint NX App Store.

    For more information, refer to How to Get the App

  • Create an access token for Google Drive. In this example, the access token name is Write Excel Data.

    For more information, refer to Access Token for Google Drive.

  • You must have Google account.
  • Upload your Microsoft Excel file — Sales Invoice.xlsx in this example — to the Google Drive folder you want to use for your app.
  • Create a folder in Google Drive to save the completed Sales Invoice.xlsx Microsoft Excel file. In this example, the folder name is Sales Invoice Result.

Good to Know

  • The Sales Invoice app is a process-based app that creates an invoice for a sale.

    For the use case in this example, this app provides a form to enter information required for the invoice. The invoice request is then routed to the Excel Write activity to populate the Microsoft Excel file Sales Invoice.xlsx with information about the customer that is entered in the eForm.

  • This topic uses the Excel Write activity in the Document tab. There are also versions of the Excel Read and Excel Write activities in the SharePoint tab. In general, examples are not provided for most third-party intergrations, including SharePoint. For information that is specific to the SharePoint versions of these activities, refer to Where Can I Find Information and Examples for Third-Party Integrations?

Step 1: Create a Microsoft Excel Template File

First, create and configure a Microsoft Excel file to save the data from an eForm.

Good to Know

How to Start

  1. Open Microsoft Excel.
  2. Click Blank workbook.
  3. Save an Excel file with the file name Sales Invoice.xlsx.

Procedure

  1. In the Sales Invoice.xlsx file, enter the column names in this table:
    Column Names
    Account Name
    E-mail ID
    Phone
    Cutomer PO Number
    PO Amount
    Payment Terms

    Sales Invoice Excel Template
  2. Name the cells where you want to store the data from the eForm.

    Each cell where you want to store data must have a name. For this example, use the names shown in this table.

    Cell Name
    A2 AccountName
    B2 EmailId
    C2 Phone
    D2 CustomerPoNumber
    E2 PoAmount
    F2 PaymentTerms
    1. Select the cell A2.

      Excel Read General Configuration screen
    2. To name the cell, in the Name Box, enter AccountName.

      Excel Read General Configuration screen
    3. Repeat these steps for each cell.

Step 2: Configure an Excel Write Activity

Next, add and configure an Excel Write activity.

How to Start

  1. Click App Builder.

    Build Apps screen
  2. On the App Explorer screen, click Sales Invoice > Processes > Sales Invoice > Process Model > Sales Invoice.

    Sales Invoice app

Procedure

  1. On the Document tab, drag the Excel Write Excel Write icon activity onto your process.

    Sales Invoice Process Model
  2. On the General Configuration screen, in the Display Name field, enter Write Sales Invoice Excel.

    Excel Write General Configuration screen
  3. Click Next.
  4. On the Source Repository screen, select Google Drive.

    Source Repository screen
  5. Click Next.
  6. On the Design Time Template Configuration screen, in the Google Drive list, select Write Excel Data access token.

    Design Time Template Configuration screen
  7. In the File Path section, select Choose File Path > Sales Invoice.xlsx.

    As a prerequisite, Sales Invoice.xlsx must exist in Google Drive.


    Design Time Template Configuration screen
  8. Click Next.
  9. On the Runtime Repository screen, select Google Drive.

    Runtime Repository screen
  10. On the Runtime Template Configuration screen, in the Google Drive list, select Write Excel Data.

    Runtime Template Configuration screen
  11. In the File Path section, select Choose File Path > Sales Invoice.xlsx.

    Runtime Repository screen
  12. Click Next.
  13. On the Target Repository, select Google Drive.

    Target Repository screen
  14. On the Target Repository Configuration screen, in the Google Drive list, select Write Excel Data.

    Target Repository Configuration screen
  15. In the File Path section, select Choose File Path > Sales Invoice Result folder.

    The Sales Invoice Result folder stores the result of the Sales Invoice.xlsx Microsoft Excel file.

    As a prerequisite, the Sales Invoice Result folder must exist in Google Drive.


    Target Repository Configuration screen
  16. Click Next.
  17. On the Excel Write Configuration screen, in the Save To, in the Enter File Name field, enter Result Sales Invoice Excel.

    The Result Sales Invoice Excel is a file name used to save the result of the Sales Invoice.xlsx Microsoft Excel file.


    Target Repository Configuration screen
  18. In the Fields Mapping field, click Schema Mapping Schema Mapping icon.

    Target Repository Configuration screen
  19. On the Schema Mapper screen, map the form fields to the cells in the Excel file as shown in this table.

    These entity fields are associated with form controls in the Sales Invoice app.

    Form Fields Excel Template Cell Name
    AccountName__u AccountName
    CutomerPONumber__u CutomerPoNumber
    EmailId__u EmailId
    PaymentTerms__u PaymentTerms
    Phone__u Phone
    POAmount__u PoAmount

    Schema Mapper screen
  20. Click Finish.

    The Process Builder shows with the Excel Write activity.


    Contact Crisis Management COVID 19 Process Model

Step 3: (Optional) Complete the Process

Next, save and validate your process model.

Procedure

  1. To validate a process, refer to Validate a Process.
  2. On the Save quick menu, click Save and Check In Save and Check In icon.

    Sales Invoice Process screen

Step 4: (Optional) Publish and Run the App

Next, publish the app so people can use it.

Procedure

  1. To publish the app, refer to Publish a process-based app.
  2. To run the app, refer to Start your process.
  3. Complete your Sales Invoice Request form and submit the form.

    Sales Invoice Request Form
  4. Go to Google Drive, and open the Sales Invoice Result folder > Result Sales Invoice Excel file.

    As a prerequisite, the Sales Invoice Result folder must exist in Google Drive.

    When you open the Result Sales Invoice Excel file, it shows the data from the Sales Invoice Request form.


    Google Drive

How to Get the App

This example is based on the Sales Invoice app from the AgilePoint NX App Store. Use this procedure to get the app.

Prerequisites

Good to Know

  • This procedure is optional. It is provided so you can test the example with an out-of-the-box app.
  • This is part of the Background and Setup procedures, but many users preferred this step to appear after the example, not before it.

Procedure

  1. Click App Store App Store icon.

    App Store
  2. In the CATEGORY section, click Sales.

    App Store screen
  3. Click the Sales Invoice app.

    Sales Invoice screen
  4. On the Sales Invoice screen, click Add.

    Sales Invoice screen
  5. On the Trust App screen, click Trust it.
  6. Click Return to site.