<jupyter_start><jupyter_text>1. Install DependenciesFirst install the libraries needed to execute recipes, this only needs to be done once, then click play.<jupyter_code>!pip install git+https://github.com/google/starthinker<jupyter_output><empty_output><jupyter_text>2. Get Cloud Project IDTo run this recipe [requires a Google Cloud Project](https://github.com/google/starthinker/blob/master/tutorials/cloud_project.md), this only needs to be done once, then click play.<jupyter_code>CLOUD_PROJECT = 'PASTE PROJECT ID HERE'

print("Cloud Project Set To: %s" % CLOUD_PROJECT)<jupyter_output><empty_output><jupyter_text>3. Get Client CredentialsTo read and write to various endpoints requires [downloading client credentials](https://github.com/google/starthinker/blob/master/tutorials/cloud_client_installed.md), this only needs to be done once, then click play.<jupyter_code>CLIENT_CREDENTIALS = 'PASTE CREDENTIALS HERE'

print("Client Credentials Set To: %s" % CLIENT_CREDENTIALS)<jupyter_output><empty_output><jupyter_text>4. Enter SmartSheet Sheet To BigQuery ParametersMove sheet data into a BigQuery table. 1. Specify SmartSheet token. 1. Locate the ID of a sheet by viewing its properties. 1. Provide a BigQuery dataset ( must exist ) and table to write the data into. 1. StarThinker will automatically map the correct schema.Modify the values below for your use case, can be done multiple times, then click play.<jupyter_code>FIELDS = {
  'auth_read': 'user',  # Credentials used for reading data.
  'auth_write': 'service',  # Credentials used for writing data.
  'token': '',  # Retrieve from SmartSheet account settings.
  'sheet': '',  # Retrieve from sheet properties.
  'dataset': '',  # Existing BigQuery dataset.
  'table': '',  # Table to create from this report.
  'schema': '',  # Schema provided in JSON list format or leave empty to auto detect.
  'link': True,  # Add a link to each row as the first column.
}

print("Parameters Set To: %s" % FIELDS)<jupyter_output><empty_output><jupyter_text>5. Execute SmartSheet Sheet To BigQueryThis does NOT need to be modified unless you are changing the recipe, click play.<jupyter_code>from starthinker.util.project import project
from starthinker.script.parse import json_set_fields

USER_CREDENTIALS = '/content/user.json'

TASKS = [
  {
    'smartsheet': {
      'auth': 'user',
      'token': {'field': {'name': 'token','kind': 'string','order': 2,'default': '','description': 'Retrieve from SmartSheet account settings.'}},
      'sheet': {'field': {'name': 'sheet','kind': 'string','order': 3,'description': 'Retrieve from sheet properties.'}},
      'link': {'field': {'name': 'link','kind': 'boolean','order': 7,'default': True,'description': 'Add a link to each row as the first column.'}},
      'out': {
        'bigquery': {
          'auth': 'user',
          'dataset': {'field': {'name': 'dataset','kind': 'string','order': 4,'default': '','description': 'Existing BigQuery dataset.'}},
          'table': {'field': {'name': 'table','kind': 'string','order': 5,'default': '','description': 'Table to create from this report.'}},
          'schema': {'field': {'name': 'schema','kind': 'json','order': 6,'description': 'Schema provided in JSON list format or leave empty to auto detect.'}}
        }
      }
    }
  }
]

json_set_fields(TASKS, FIELDS)

project.initialize(_recipe={ 'tasks':TASKS }, _project=CLOUD_PROJECT, _user=USER_CREDENTIALS, _client=CLIENT_CREDENTIALS, _verbose=True, _force=True)
project.execute(_force=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13813559322033897, 'fraction_numerical': 0.00423728813559322, 'mean_word_length': 5.105172413793103, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '2135', 'license': 'Apache-2.0', 'n_tokens_mistral': 1093, 'n_tokens_neox': 1062, 'n_words': 342, 'path': 'colabs/smartsheet_to_bigquery.ipynb', 'repo_name': 'fivestones-apac/starthinker', 'size': '3540'}