(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 Workspace.

  • 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.

    CellName
    A2AccountName
    B2EmailId
    C2Phone
    D2CustomerPoNumber
    E2PoAmount
    F2PaymentTerms
    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 Workspace 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 Workspace 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. Click Next.
  15. On the Target Repository Configuration screen, in the Google Workspace list, select Write Excel Data.

    Target Repository Configuration screen
  16. 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
  17. Click Next.
  18. 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
  19. In the Fields Mapping field, click Schema Mapping Schema Mapping icon.

    Target Repository Configuration screen
  20. 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 FieldsExcel Template Cell Name
    AccountName__uAccountName
    CutomerPONumber__uCutomerPoNumber
    EmailId__uEmailId
    PaymentTerms__uPaymentTerms
    Phone__uPhone
    POAmount__uPoAmount

    Schema Mapper screen
  21. Click Finish.

    The Process Builder shows with the Excel Write activity.


    Contact Crisis Management COVID 19 Process Model

Step 4: (Optional) Run the App

Next, run the app.

Procedure

  1. To run the app, refer to My Apps screen (Modern).
  2. Complete your Sales Invoice Request form and submit the form.

    Sales Invoice Request Form
  3. 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.