def setUp(self):
self.google_sheets = GoogleSheets()
self.spreadsheet_id = self.google_sheets.create_spreadsheet(
'parsons_test_01')
self.test_table = Table([
{
'first': 'Bob',
'last': 'Smith'
},
{
'first': 'Sue',
'last': 'Doe'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id,
self.test_table)
self.second_sheet_title = "2nd"
self.google_sheets.add_sheet(self.spreadsheet_id,
self.second_sheet_title)
self.second_test_table = Table([
{
'city': 'San Francisco',
'state': 'SF'
},
{
'city': 'Chicago',
'state': 'IL'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id,
self.second_test_table, 1)
class TestGoogleSheets(unittest.TestCase):
def setUp(self):
self.google_sheets = GoogleSheets()
self.spreadsheet_id = self.google_sheets.create_spreadsheet(
'parsons_test_01')
self.test_table = Table([
{
'first': 'Bob',
'last': 'Smith'
},
{
'first': 'Sue',
'last': 'Doe'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id,
self.test_table)
self.second_sheet_title = "2nd"
self.google_sheets.add_sheet(self.spreadsheet_id,
self.second_sheet_title)
self.second_test_table = Table([
{
'city': 'San Francisco',
'state': 'SF'
},
{
'city': 'Chicago',
'state': 'IL'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id,
self.second_test_table, 1)
def tearDown(self):
# self.google_sheets.delete_spreadsheet(self.spreadsheet_id)
pass
def test_read_worksheet(self):
# This is the spreadsheet called "Legislators 2017 (Test sheet for Parsons)"
table = self.google_sheets.get_worksheet(
'1Y_pZxz-8JZ9QBdq1pXuIk2js_VXeymOUoZhUp1JVEg8')
self.assertEqual(541, table.num_rows)
def test_read_sheet(self):
# Deprecated in Parsons v0.14
# This is the spreadsheet called "Legislators 2017 (Test sheet for Parsons)"
table = self.google_sheets.read_sheet(
'1Y_pZxz-8JZ9QBdq1pXuIk2js_VXeymOUoZhUp1JVEg8')
self.assertEqual(541, table.num_rows)
def test_read_nonexistent_worksheet(self):
self.assertRaises(gspread.exceptions.APIError,
self.google_sheets.read_sheet, 'abc123')
def test_create_spreadsheet(self):
# Created as part of setUp
self.assertIsNotNone(self.spreadsheet_id)
def test_add_sheet(self):
# Sheet added as part of setUp
# Also tests get_sheet_index_with_title
idx = self.google_sheets.get_worksheet_index(self.spreadsheet_id,
self.second_sheet_title)
self.assertEqual(1, idx)
def test_get_sheet_index_with_bogus_title(self):
self.assertRaises(ValueError, self.google_sheets.get_worksheet_index,
self.spreadsheet_id, 'abc123')
def test_read_worksheet_with_title(self):
table = self.google_sheets.get_worksheet(self.spreadsheet_id,
self.second_sheet_title)
self.assertEqual(self.second_test_table.columns, table.columns)
def test_append_to_spreadsheet(self):
# BROKEN TEST!
append_table = Table([
{
'first': 'Jim',
'last': 'Mitchell'
},
{
'first': 'Lucy',
'last': 'Simpson'
},
])
self.google_sheets.append_to_sheet(self.spreadsheet_id, append_table)
result_table = self.google_sheets.read_sheet(self.spreadsheet_id)
self.assertEqual(append_table.columns, result_table.columns)
# We should now have rows from both tables
self.assertEqual(self.test_table.num_rows + append_table.num_rows,
result_table.num_rows)
# First check that we didn't muck with the original data
for i in range(self.test_table.num_rows):
self.assertEqual(self.test_table.data[i], result_table.data[i])
orig_row_count = self.test_table.num_rows
# Then check that we appended the data properly
for i in range(append_table.num_rows):
self.assertEqual(append_table.data[i],
result_table.data[orig_row_count + i])
# Test that we can append to an empty sheet
self.google_sheets.add_sheet(self.spreadsheet_id, 'Sheet3')
self.google_sheets.append_to_sheet(self.spreadsheet_id, append_table)
def test_append_user_entered_to_spreadsheet(self):
# Testing whether we can insert formulas with user_entered_value
self.google_sheets.add_sheet(self.spreadsheet_id, 'Sheet3')
append_table = Table([
{
'col1': 3,
'col2': 9,
'col3': '=A2*B2'
},
{
'col1': 'Buda',
'col2': 'Pest',
'col3': '=A3&LOWER(B3)'
},
])
self.google_sheets.append_to_sheet(self.spreadsheet_id,
append_table,
2,
user_entered_value=True)
result_table = self.google_sheets.read_sheet(self.spreadsheet_id, 2)
# Get the values from col3 which has fomulas
formula_vals = [row['col3'] for row in result_table]
# Test that the value is what's expected from each formula
self.assertEqual(formula_vals[0], '27')
self.assertEqual(formula_vals[1], 'Budapest')
def test_overwrite_spreadsheet(self):
new_table = Table([
{
'city': 'San Francisco',
'state': 'CA'
},
{
'city': 'Miami',
'state': 'FL'
},
{
'city': 'San Antonio',
'state': 'TX'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id, new_table)
result_table = self.google_sheets.read_sheet(self.spreadsheet_id)
assert_matching_tables(new_table, result_table)
def test_share_spreadsheet(self):
# Test that sharing of spreadsheet works as intended.
self.google_sheets.share_spreadsheet(self.spreadsheet_id,
'*****@*****.**',
role='reader',
notify=True)
permissions = self.google_sheets.get_spreadsheet_permissions(
self.spreadsheet_id)
self.assertIn('*****@*****.**', permissions['emailAddress'])
logger.addHandler(_handler)
logger.setLevel('INFO')
# To use the MySQL connector, set the environment variables:
# MYSQL_USERNAME
# MYSQL_PASSWORD
# MYSQL_HOST
# MYSQL_DB
# MYSQL_PORT
# To use the Google Sheets connector, set the GOOGLE_DRIVE_CREDENTIALS environment variable.
# More on environmental variables:
# https://move-coop.github.io/parsons/html/use_cases/contribute_use_cases.html#sensitive-information
# Instantiate classes
mysql = MySQL()
gsheets = GoogleSheets()
# Configuration Variables
# FOLDER_ID is the ID of the Google Drive folder the Google Sheets workbook will be created.
FOLDER_ID = 'enter_id_here'
# TITLE is the name of the Google Sheets workbook the script will create.
TITLE = 'sheet_title_here'
# TAB_LABEL is the name of the tab where your query results will appear in Google Sheets.
TAB_LABEL = 'tab_label_here'
# QUERY is the SQL query we will run against the MYSQL database.
QUERY = '''-- Enter SQL here'''
# Function to add data to spreadsheet tab.
# There is a limit to the number of calls per minute,
# so we use request_count to set a maximum number of tries
# Set up logger
# -------------------------------------------------------------------------------
logger = logging.getLogger(__name__)
_handler = logging.StreamHandler()
_formatter = logging.Formatter("{levelname} {message}", style="{")
_handler.setFormatter(_formatter)
logger.addHandler(_handler)
logger.setLevel("INFO")
TOOL_ID = 39195
# Instantiate parsons New Mode class
newmode = Newmode(
api_user=new_mode_username, api_password=new_mode_password
)
# Instantiate parsons GSheets class
parsons_sheets = GoogleSheets(google_keyfile_dict=creds)
def get_target_with_sleep(target_id_list, sleep=5):
target_names_list = []
for target_id in target_id_list:
try:
target = newmode.get_target(target_id)
target_name = target["full_name"]
target_names_list.append(target_name)
except:
logger.info(f"API being rate limited, sleeping for {sleep} and trying again")
time.sleep(sleep)
return target_names_list
def transform_outreaches(outreaches):
class TestGoogleSheets(unittest.TestCase):
def setUp(self):
self.google_sheets = GoogleSheets()
self.spreadsheet_id = self.google_sheets.create_spreadsheet(
'Parsons Test')
self.test_table = Table([
{
'first': 'Bob',
'last': 'Smith'
},
{
'first': 'Sue',
'last': 'Doe'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id,
self.test_table)
self.second_sheet_title = "2nd sheet"
self.google_sheets.add_sheet(self.spreadsheet_id,
self.second_sheet_title)
self.second_test_table = Table([
{
'city': 'San Francisco',
'state': 'SF'
},
{
'city': 'Chicago',
'state': 'IL'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id,
self.second_test_table, 1)
def tearDown(self):
self.google_sheets.delete_spreadsheet(self.spreadsheet_id)
def test_read_sheet(self):
# This is the spreadsheet called "Legislators 2017 (Test sheet for Parsons)"
table = self.google_sheets.read_sheet(
'1Y_pZxz-8JZ9QBdq1pXuIk2js_VXeymOUoZhUp1JVEg8')
self.assertEqual(541, table.num_rows)
def test_read_nonexistent_sheet(self):
self.assertRaises(gspread.exceptions.APIError,
self.google_sheets.read_sheet, 'abc123')
def test_create_spreadsheet(self):
# Created as part of setUp
self.assertIsNotNone(self.spreadsheet_id)
def test_add_sheet(self):
# Sheet added as part of setUp
# Also tests get_sheet_index_with_title
idx = self.google_sheets.get_sheet_index_with_title(
self.spreadsheet_id, self.second_sheet_title)
self.assertEqual(1, idx)
def test_get_sheet_index_with_bogus_title(self):
self.assertRaises(ValueError,
self.google_sheets.get_sheet_index_with_title,
self.spreadsheet_id, 'abc123')
def test_read_sheet_with_title(self):
table = self.google_sheets.read_sheet_with_title(
self.spreadsheet_id, self.second_sheet_title)
self.assertEqual(self.second_test_table.columns, table.columns)
def test_append_to_spreadsheet(self):
append_table = Table([
{
'first': 'Jim',
'last': 'Mitchell'
},
{
'first': 'Lucy',
'last': 'Simpson'
},
])
self.google_sheets.append_to_sheet(self.spreadsheet_id, append_table)
result_table = self.google_sheets.read_sheet(self.spreadsheet_id)
self.assertEqual(append_table.columns, result_table.columns)
# We should now have rows from both tables
self.assertEqual(self.test_table.num_rows + append_table.num_rows,
result_table.num_rows)
# First check that we didn't muck with the original data
for i in range(self.test_table.num_rows):
self.assertEqual(self.test_table.data[i], result_table.data[i])
orig_row_count = self.test_table.num_rows
# Then check that we appended the data properly
for i in range(append_table.num_rows):
self.assertEqual(append_table.data[i],
result_table.data[orig_row_count + i])
def test_overwrite_spreadsheet(self):
new_table = Table([
{
'city': 'San Francisco',
'state': 'CA'
},
{
'city': 'Miami',
'state': 'FL'
},
{
'city': 'San Antonio',
'state': 'TX'
},
])
self.google_sheets.overwrite_sheet(self.spreadsheet_id, new_table)
result_table = self.google_sheets.read_sheet(self.spreadsheet_id)
self.assertEqual(new_table.columns, result_table.columns)
self.assertEqual(new_table.num_rows, result_table.num_rows)
for i in range(new_table.num_rows):
self.assertEqual(new_table.data[i], result_table.data[i])