# Connecting to Smartsheet
Smartsheet is a work management and collaboration SaaS product. Fundamentally, Smartsheet allows users to use spreadsheet-like objects to create, store, and utilize business data.
**Topics**
+ [
# AWS Glue support for Smartsheet
](smartsheet-support.md)
+ [
# Policies containing the API operations for creating and using connections
](smartsheet-configuring-iam-permissions.md)
+ [
# Configuring Smartsheet
](smartsheet-configuring.md)
+ [
# Configuring Smartsheet connections
](smartsheet-configuring-connections.md)
+ [
# Reading from Smartsheet entities
](smartsheet-reading-from-entities.md)
+ [
# Smartsheet connection options
](smartsheet-connection-options.md)
+ [
# Creating an Smartsheet account
](smartsheet-create-account.md)
+ [
# Limitations
](smartsheet-connector-limitations.md)
# AWS Glue support for Smartsheet
AWS Glue supports Smartsheet as follows:
**Supported as a source?**
Yes. You can use AWS Glue ETL jobs to query data from Smartsheet.
**Supported as a target?**
No.
**Supported Smartsheet API versions**
v2.0
# Policies containing the API operations for creating and using connections
The following sample policy describes the required AWS permissions for creating and using connections. If you are creating a new role, create a policy that contains the following:
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"glue:ListConnectionTypes",
"glue:DescribeConnectionType",
"glue:RefreshOAuth2Tokens",
"glue:ListEntities",
"glue:DescribeEntity"
],
"Resource": "*"
}
]
}
```
------
If you don't want to use the preceding method, alternatively, use the following managed IAM policies:
+ [ AWSGlueServiceRole ](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole) – Grants access to resources that various AWS Glue processes require to run on your behalf. These resources include AWS Glue, Amazon S3, IAM, CloudWatch Logs, and Amazon EC2. If you follow the naming convention for resources specified in this policy, AWS Glue processes have the required permissions. This policy is typically attached to roles specified when defining crawlers, jobs, and development endpoints.
+ [ AWSGlueConsoleFullAccess ](https://console.aws.amazon.com/iam/home#policies/arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess) – Grants full access to AWS Glue resources when an identity that the policy is attached to uses the AWS Management console. If you follow the naming convention for resources specified in this policy, users have full console capabilities. This policy is typically attached to users of the AWS Glue console.
# Configuring Smartsheet
Before you can use AWS Glue to transfer from Smartsheet, you must meet the following requirements:
## Minimum requirements
+ You have an Smartsheet account with email and password. For more information about creating an account, see [Creating a Smartsheet account](smartsheet-create-account.md).
+ Your Smartsheet account has API access with valid license.
+ Your Smartsheet account has **Pro pricing** plan for `Sheets` entity and Enterprise pricing plan with Event Reporting Add-On for `Events` entity.
If you meet these requirements, you’re ready to connect AWS Glue to your Smartsheet account. For typical connections, you don't need do anything else in Smartsheet.
# Configuring Smartsheet connections
Smartsheet supports `AUTHORIZATION_CODE` grant type for OAuth2.
This grant type is considered “three-legged” `OAuth` as it relies on redirecting users to the third-party authorization server to authenticate the user. Users may opt to create their own connected app in Smartsheet and provide their own client ID and client secret when creating connections through the AWS Glue console. In this scenario, they will still be redirected to Smartsheet to login and authorize AWS Glue to access their resources.
This grant type results in a refresh token and access token. The access token is short lived, and may be refreshed automatically without user interaction using the refresh token.
For public Smartsheet documentation on creating a connected app for AUTHORIZATION\$1CODE OAuth flow, see [Smartsheet APIs](https://smartsheet.redoc.ly/#section/OAuth-Walkthrough) .
To configure a Smartsheet connection:
1. In AWS Secrets Manager, create a secret with the following details:
For customer managed connected app – Secret should contain the connected app Consumer Secret with `USER_MANAGED_CLIENT_APPLICATION_CLIENT_SECRET` as key.
**Note**
It is a must to create a secret per connection in AWS Glue.
1. In the AWS Glue Studio, create a connection under **Data Connections** by following the steps below:
1. When selecting a **Connection type**, select Smartsheet.
1. Provide the `instanceUrl` of the Smartsheet you want to connect to.
1. Select the IAM role for which AWS Glue can assume and has permissions for following actions:
------
#### [ JSON ]
****
```
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"secretsmanager:DescribeSecret",
"secretsmanager:GetSecretValue",
"secretsmanager:PutSecretValue",
"ec2:CreateNetworkInterface",
"ec2:DescribeNetworkInterfaces",
"ec2:DeleteNetworkInterface"
],
"Resource": "*"
}
]
}
```
------
1. Select the `secretName` which you want to use for this connection in AWS Glue to put the tokens.
1. Select the network options if you want to use your network.
1. Grant the IAM role associated with your AWS Glue job permission to read `secretName`.
# Reading from Smartsheet entities
**Prerequisites**
A `Smartsheet` Object you would like to read from. Refer the supported entities table below to check the available entities.
**Supported entities**
| Entity | Can be Filtered | Supports Limit | Supports Order By | Supports Select \$1 | Supports Partitioning |
| --- | --- | --- | --- | --- | --- |
| List Sheet | Yes | Yes | No | Yes | No |
| Row Metadata | Yes | Yes | No | Yes | No |
| Sheet Metadata | No | No | No | Yes | No |
| Sheet Data | Yes | Yes | Yes | Yes | No |
| Event | Yes | Yes | No | Yes | No |
**Example**
```
Smartsheet_read = glueContext.create_dynamic_frame.from_options(
connection_type="smartsheet",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "list-sheets",
"API_VERSION": "2.0",
"INSTANCE_URL": "https://api.smartsheet.com"
})
```
**Smartsheet entity and field details**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/glue/latest/dg/smartsheet-reading-from-entities.html)
**Entities with dynamic metadata:**
For the following entity, Smartsheet provides an endpoint to fetch metadata dynamically, allowing operator support to be captured at the datatype level.
| Entity | Data Type | Supported Operators |
| --- | --- | --- |
| Sheet Data | String | NA |
| Sheet Data | Long | "=" |
| Sheet Data | Integer | NA |
| Sheet Data | DateTime | > |
**Example**
```
Smartsheet_read = glueContext.create_dynamic_frame.from_options(
connection_type="smartsheet",
connection_options={
"connectionName": "connectionName",
"ENTITY_NAME": "list-sheets",
"API_VERSION": "2.0",
"INSTANCE_URL": "https://api.smartsheet.com"
}
```
# Smartsheet connection options
The following are connection options for Smartsheet:
+ `ENTITY_NAME`(String) – (Required) Used for Read/Write. The name of your Object in Smartsheet.
+ `API_VERSION`(String) – (Required) Used for Read/Write. Smartsheet Rest API version you want to use. For example: v2.0.
+ `INSTANCE_URL`(String) – (Required) Used for Read. Smartsheet instance URL.
+ `SELECTED_FIELDS`(List) – Default: empty(SELECT \$1). Used for Read. Columns you want to select for the object.
+ `FILTER_PREDICATE`(String) – Default: empty. Used for Read. It should be in the Spark SQL format.
+ `QUERY`(String) – Default: empty. Used for Read. Full Spark SQL query.
# Creating an Smartsheet account
1. Sign up for a Smartsheet account by accessing [Smartsheet sing-up page](https://app.smartsheet.com/home).
1. Choose **Create one** to create a new account, or sign in using your registered Google, Microsoft, or Apple account.
1. Enter your work email address when prompted.
1. Choose **Continue** and if required, verify your identity.
1. Open the confirmation email from Smartsheet, and choose the confirmation link to verify your account.
You will be subscribed to the Trial Plan by default.
1. In the bottom-left corner, choose the **Account** icon and choose **Add Licenses/Upgrade** to upgrade your pricing plan.
**Note**
This is required for accessing **Event Reporting**, which is an add-on in the **Enterprise** plan.
1. Under the **Enterprise** plan, choose **Contact Us** to request an account upgrade from the support team.
1. In the support request form, provide the required details and your requirements to upgrade the plan.
This completes the upgrade to **Enterprise** plan.
**Creating `OAuth2.0` credentials**
1. After upgrading your account’s pricing plan to get access to the **Developer Tools**, access [Smartsheet developers](https://developers.smartsheet.com/).
You will receive an activation email.
1. Open an activation email from Smartsheet, and choose the activation link to activate developer tools on your account.
Developers tool allows you to create the app.
1. Open the home page of your Smartsheet account and choose **Account** to check for access.
1. Choose **Developer Tools** from the services list, and enter the **Developer Profile** details.
1. Choose **Create New App**.
1. Enter the following details into the app registration form:
+ **Name** – Name of the app.
+ **Description** – Description of the app.
+ **URL** – URL that allows you to launch your app or the URL of the landing page.
+ **Contact/support** – Contact information for the support team.
+ **Redirect URL** – URL (also known as a callback URL) within your application that will receive the [OAuth 2.0 ](https://.console.aws.amazon.com/appflow/oauth)credentials.
1. Choose **Save**.
Smartsheet assigns a client ID and client secret to your app. Record these values for the next steps. You can also look them up again later in the **Developer Tools** section.
# Limitations
Smartsheet doesn't support field-based or record-based partitioning.