Edit Spreadsheet

This action lets you edit an Excel spreadsheet (XLSX file format) by adding data to a range of cells. Any existing data in those cells will be overwritten.

Inputs

Note that the range#, input# and inputColumnOrder# inputs must come as groups — i.e. if there is a range1 input, there must also be an input1 and optionally an inputColumnOrder1. The same goes for range2, range3, etc.

Key

Value

workbookUrl

Required

The URL of the Excel workbook file containing the spreadsheet that you wish to edit. The file should be in XLSX format.

spreadsheetName

Required

The name of the spreadsheet that you wish to edit within the workbook.

range#

Required

The range of cells that you want to edit within the chosen spreadsheet, using Excel's A1 notation. This can be a single cell (e.g. C9) or a span of multiple cells (e.g. A2:C9).

Note that the range's end cell is not enforced! For instance if you specify A2:C9 as your range and the DataSet in the corresponding input# contains 20 rows and 10 columns, then the range A2:J21 will be filled in to accommodate all the rows and columns in the DataSet.

input#

Required

If the associated range is a single cell, this can be any input. If the range spans multiple cells, this should be a DataSet file URL.

inputColumnOrder#

Optional

The order in which DataSet's columns should be added to the spreadsheet, separated by pipe characters. For instance, if the value is SKU|Description|PurchaseCost, that means the SKU column will be added first, then the Description column, then the PurchaseCost column. If the DataSet contains additional columns, they will be ignored.

If this input is not specified, columns are added in the order in which they appear in the DataSet file. This field is not used if the associated input# is an individual value rather than a DataSet.

Outputs

Key

Value

workbookUrl

A Excel workbook URL containing the newly edited spreadsheet.

storageFileName

The name of the Excel file contained in the workbook URL.

Usage

Excel spreadsheets are a very common way of storing data. This action lets you update an Excel spreadsheet based on form data or other GoFormz data. For instance, suppose you have a GoFormz Data Source that lists all your on-site technicians along with their region and contact info, along with a corresponding spreadsheet with the same data. You typically update the GoFormz Data Source first because you need the technician info to fill out forms, but you want that data to periodically propagate to the spreadsheet as well. The example below illustrates how you might do this.

In this example, the workbook is located on your company's website, and the worksheet is called "HVAC Technicians". The input is a DataSet file representing our GoFormz Data Source, which we obtain using the Get Data Source step. We use "A2:E7" as the range, starting on the second row because the first row contains the column names; "E7" as the endpoint of the range is arbitrary — all rows from the DataSet will be used. Finally, we specify the 5 DataSet columns that we want in the spreadsheet, and the order in which they should be added: Last Name, First Name, Region, Email, Phone.