smartsheet-apismartsheet-api-1.1

Smartsheet-API: Populating data and formulas in a sheet

UPDATE 2022-12-01

This question is now obsolete. Smartsheet API now supports the requested feature. The accepted answer contains more details on the update.

CONTEXT

A developer wishes to populate an existing smartsheet worksheet using the smartsheet API. The data to populate consists of both data and formulas.

However, according to the API documentation (http://www.smartsheet.com/developers/api-documentation) formulas cannot be added using the API.

Cells containing formulas, links to other cells, system values, or Gantt values cannot be inserted or updated through the API.

PROBLEM

Testing verifies this. Attempting to add a simple formula using the smartsheet API causes the formula to be transformed to opaque text. Inspection reveals that the formula is modified with the single quote character, which renders it as opaque text instead of a formula.

QUESTION

Is there any way (other than through manual entry) to force smartsheet to re-evaluate the opaque text inserted, so as to transform the opaque text back into a formula?

If it is not possible (other than through manual entry) is it possible to copy an existing sheet that has formulas in place, and then populate the non-formula data into the sheet, all using the smartsheet API?

GOAL

The basic goal is to find a way to populate formula data into the smartsheet application without having to require manual entry of the formula data.

Solution

  • Update: Smartsheet does now support adding or updating formulas via the API which can be found in the documentation for adding a row and updating a row.

    The main difference is to set the formula in the row object instead of setting the value.

    Original Answer

    You are correct, formulas are not currently supported via the API. Although, we do plan to add this functionality in the future.

    Currently, if you try to send a formula to the API it will be handled as a string and a single quote will be added to the beginning of the formula. Then the only way to convert the string back to a formula is to manually remove the single quote when inside the Smartsheet UI.

    Available Option

    Your suggestion to use a template will definitely work if you will always be using the same formulas. The process will look like the following:

    1. Setup a template with the formulas that you want to use.
    2. Create a new sheet from the template.
    3. Add extra data to the new sheet that the formulas will use.

    Note: rows that have never been used cannot be updated since they do not exist. So, in the template you can initialize the rows by putting a word in the locations that you want to update. For example, you could put the word "PLACEHOLDER" in all of the locations that you intend to update.

    I have added two examples below one using curl and the other using our Java SDK.

    Curl Example

    Create a new sheet from the Template. Make sure to replace YOUR_TOKEN and YOUR_TEMPLATE_OR_SHEET_ID in the below command.

    curl https://api.smartsheet.com/1.1/sheets?include=data,attachments,discussions -H "Authorization: Bearer YOUR_TOKEN" -H "Content-Type: application/json" -X POST -d '{"name":"newSheetFromTemplate","fromId":"YOUR_TEMPLATE_OR_SHEET_ID"}'

    Then take the sheet id from the response and issue a command to get the row id's.

    curl https://api.smartsheet.com/1.1/sheet/YOUR_SHEET_ID -H "Authorization: Bearer YOUR_TOKEN"

    Last, grab the row id and column id from the response and issue a command to update the appropriate cells. I'm updating two cells with the values 1 and 2 with the below command.

    curl https://api.smartsheet.com/1.1/row/YOUR_ROW_ID/cells -H "Authorization: Bearer YOUR_TOKEN" -H "Content-Type: application/json" -X PUT -d '[ {"columnId": YOUR_COLUMN_ID, "value": 1}]'
curl https://api.smartsheet.com/1.1/row/YOUR_ROW_ID/cells -H "Authorization: Bearer YOUR_TOKEN" -H "Content-Type: application/json" -X PUT -d '[ {"columnId": YOUR_COLUMN_ID, "value": 2}]'

    SDK Example

    This example requires installing our Java SDK. There is also a C# SDK that works in a very similar fashion.

    import java.util.EnumSet;
import java.util.List;

import com.smartsheet.api.Smartsheet;
import com.smartsheet.api.SmartsheetBuilder;
import com.smartsheet.api.models.Cell;
import com.smartsheet.api.models.Column;
import com.smartsheet.api.models.ObjectInclusion;
import com.smartsheet.api.models.Row;
import com.smartsheet.api.models.Sheet;
import com.smartsheet.api.SmartsheetException;

public class Test {

    public static void main(String[] args) throws SmartsheetException {
        // Setup a Smartsheet object
        Smartsheet smartsheet = new SmartsheetBuilder().setAccessToken("YOUR_TOKEN").build();

        // Create a copy of a sheet from the template
        Sheet newSheet = new Sheet.CreateFromTemplateOrSheetBuilder().setFromId(YOUR_TEMPLATE_OR_SHEET_ID).setName("newSheetName").build();
        newSheet = smartsheet.sheets().createSheetFromExisting(newSheet, EnumSet.allOf(ObjectInclusion.class));

        // Get the columns/rows/data for the sheet we just created
        newSheet = smartsheet.sheets().getSheet(newSheet.getId(), EnumSet.allOf(ObjectInclusion.class));

        // Grab the column and rows that will be updated in the new sheet
        Column column1 = newSheet.getColumnByIndex(0);
        Row row1 = newSheet.getRowByRowNumber(1);
        Row row2 = newSheet.getRowByRowNumber(2);

        // Setup two cells for the the specified columns
        List<Cell> newCell1 = new Cell.UpdateRowCellsBuilder().addCell(column1.getId(), 1).build();
        List<Cell> newCell2 = new Cell.UpdateRowCellsBuilder().addCell(column1.getId(), 2).build();

        // Update the cell for the specified row
        smartsheet.rows().updateCells(row1.getId(), newCell1);
        smartsheet.rows().updateCells(row2.getId(), newCell2);
    }
}