Reading sheet contents using SmartSheet Java SDK

Question

I am trying to read data using the Smartsheet API from Java to create different formats, such as reports & labels with the data from one row.

I've set up my IDE (NetBeans) so that the API samples work for me, but they are all about creating new sheets etc and I can not figure out how to read the contents of an existing sheet.

I would have thought that I could read the entire sheet into a java object in one line of code, but it appears more complicated than that, and I can not find any applicable documentation anywhere. The Javadoc does not say where/how to get the relevant IDs, what any of the inclusion or exclusion objects actually do, or which are required or optional etc.

Are there any examples of reading the contents of a sheet from java available?

I know that this is a bit of a broad question, but I'm totally stumped.

Answer 1

Thanks Kim!

For others, here's what worked for me. This code gets a list of the sheets in my account and displays the contents those with names starting with "Specs - " :

import com.smartsheet.api.Smartsheet;
import com.smartsheet.api.SmartsheetBuilder;
import com.smartsheet.api.SmartsheetException;
import com.smartsheet.api.models.Cell;
import com.smartsheet.api.models.Column;
import com.smartsheet.api.models.PagedResult;
import com.smartsheet.api.models.Row;
import com.smartsheet.api.models.Sheet;
import com.smartsheet.api.oauth.Token;
import java.util.List;
import java.util.Objects;
import java.util.logging.Level;
import java.util.logging.Logger;


public class SampleCode {

    /*
        Show the contenst of all sheets whose name starts with "Specs - "
    */
    public static void main(String[] args) {

        final String delimiter = ", ";

        // Create a Smartsheet object with our Access Token
        Token token = new Token();
        token.setAccessToken(Private.TOKEN);
        Smartsheet smartsheet = new SmartsheetBuilder().setAccessToken(token.getAccessToken()).build();

        //get a paged list of all Sheets, using null Source Inclusion & Pagination parameters
        PagedResult<Sheet> homeSheets = new PagedResult<>();
        try {
            homeSheets = smartsheet.sheetResources().listSheets(null, null);
        } catch (SmartsheetException ex) {
            Logger.getLogger(SampleCode.class.getName()).log(Level.SEVERE, null, ex);
        }
        // get a Java List<Sheet> from the PagedResult<Sheet>
        List<Sheet> sheetInfoList = homeSheets.getData();

        // Loop through each sheet in the list
        for (Sheet sheetInfo : sheetInfoList) {

            String sheetName = sheetInfo.getName();

            // Show data for all sheets with names that match our pattern
            if (sheetName.startsWith("Specs - ")) {

                // get the sheet object, with no optional includes or excludes
                Sheet theSheet = null;
                try {
                    theSheet = smartsheet.sheetResources().getSheet(sheetInfo.getId(), null, null, null, null, null, null, null);
                } catch (SmartsheetException ex) {
                    Logger.getLogger(SampleCode.class.getName()).log(Level.SEVERE, null, ex);
                }

                // Print the sheets name
                System.out.println("\nSheet: " + theSheet.getName() + "\n");

                // Print the column titles as a delimited line of text.
                List<Column> columnList = theSheet.getColumns();
                String columnHeader = null;
                for (Column col : columnList) {
                    columnHeader = columnHeader == null ? col.getTitle() : columnHeader + delimiter + col.getTitle();
                }
                System.out.println(columnHeader);

                // Print each row as a delimited line of text.
                List<Row> rowList = theSheet.getRows();
                for (Row row : rowList) {
                    List<Cell> cellList = row.getCells();
                    String rowOutput = null;
                    for (Cell cell : cellList) {
                        String cellOutput = Objects.toString(cell.getValue() != null ? cell.getValue() : cell.getDisplayValue());
                        rowOutput = rowOutput == null ? cellOutput : rowOutput + delimiter + cellOutput;
                    }
                    System.out.println(rowOutput);
                }
            }
        }
    }
}

Answer 2

The Smartsheet API documentation contains sample code that shows how to use the Java SDK. The Java Sample Code section in the docs describes how to establish the connection, etc. Then, each operation within in the "API Reference" section shows sample code (in the "Java" tab of the panel on the right side of the page) for executing the operation using the Java SDK.

To get Sheet data you'll use the "Get Sheet" operation. As described in the API docs, here's the Java SDK sample code for that operation:

// Get sheet (omit all parameters).
smartsheet.sheetResources().getSheet(sheetId, null, null, null, null, null, null, null);

The "sheetId" parameter should be the ID of the Sheet which you want to retrieve. You can either get this ID programmatically (i.e., by using an operation like "List Sheets", for example) -- or you can get it manually via the Smartsheet UI as described in this help article. The other parameters (all set to "null" in the sample code) represent the 7 parameters described in the API documentation for this operation. I imagine intellisense in your IDE should indicate the sequence of those parameters expected by the getSheet function, as well as valid values for each parameter (but the API docs will explain the meaning of each parameter).