List of methods for SpreadsheetApp.
| Methods for SpreadsheetApp object | return type |
| create() | Spreadsheet |
| add_permission() | |
| open_by_id() | Spreadsheet |
| open_by_url() | Spreadsheet |
For authentication, please refer to the authentication tutorial.
List of methods for Spreadsheet object
| Methods for Spreadsheet object | return type |
| get_sheets() | List[Sheet] |
| get_sheet_by_name() | Sheet |
| get_sheet_by_id() | Sheet |
| create_sheets() | List[Sheet] |
| duplicate_sheet() | Sheet |
| commit() |
List of methods for Sheet object
| Methods for Sheet object | return type |
| get_range() | Range |
| get_range_from_a1() | Range |
| get_data_range() | Range |
| get_max_rows() | Integer |
| get_max_columns() | Integer |
List of methods for Range object
| Methods for Range object | return type |
| get_values() | List[List] |
| get_notes() | List[List] |
| get_backgrounds() | List[List] |
| get_font_colors() | List[List] |
| set_values() | |
| set_notes() | |
| set_backgrounds() | |
| set_font_colors() | |
| set_value() | |
| set_note() | |
| set_background() | |
| set_font_color() | |
| commit() | |
| get_row() | Integer |
| get_column() | Integer |
| get_max_row() | Integer |
| get_max_column() | Integer |
| get_cell() | Range |
| add_dropdown() | Integer |
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.create(name='my spreadsheet', editor='[email protected]')It is highly recommended to add your email as an editor. This will make your email the owner of the newly created spreadsheet instead of the service account user from your secret.json. As a result, you will be able to find the created spreadsheets in your Google Drive.
This method will give ownership to any user for any spreadsheets created by the service account. Useful, if you have not indicated an editor in the create() method.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
sa.add_permission(file_id='<spreadsheet_id>', default_owner='[email protected]')from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')Returns a Spreadsheet object.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_url(url='http://<spreadsheet url>')Returns a Spreadsheet object.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheets = spreadsheet.get_sheets()from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet1 = spreadsheet.get_sheet_by_name('Sheet1')from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet1 = spreadsheet.get_sheet_by_id('<sheet_id>')from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
new_sheets = spreadsheet.create_sheets(['my_first_sheet', 'my_second_sheet'])It returns a list of Sheet objects in the same order of the new sheet names list given as parameter.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
cloned_sheet = spreadsheet.duplicate_sheet(
new_sheet_name='cloned name',
sheet_name='original sheet'
)cloned_sheet in that case will return the Sheet object of the new cloned sheet.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
# todo: figure out if neededfrom sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet1 = spreadsheet.get_sheet_by_name('Sheet1')
# to get cell A1
A1_cell = sheet1.get_range(row=1, column=1)
# to get cell C5
C5_cell = sheet1.get_range(row=5, column=3)
# to get range A1:A2
A1A2_range = sheet1.get_range(
row=1,
column=1,
number_of_column=2
)
# to get range A1:B2
A1B2_range = sheet1.get_range(
row=1,
column=1,
number_of_row=2
number_of_column=2
)
# to get range C5:F10"
A1B2_range = sheet1.get_range(
row=5,
column=3,
number_of_row=6
number_of_column=4
)from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet1 = spreadsheet.get_sheet_by_name('Sheet1')
# to get cell A1
A1_cell = sheet1.get_range_from_a1(a1_notification='A1')
# to get cell A3:B5
A3_B5_range = sheet1.get_range_from_a1(a1_notification='A3:B5')from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet = spreadsheet.get_sheet_by_name('Sheet1')
data_range = sheet.get_data_range()This method is particularly useful when you're not quite sure how many rows you have in your sheet. Under the hood, this method actually makes a request to the sheet and figure out the A1 notification of the range containing data.
Method to return the last row in sheet. this does not necessarily means a row with data. An empty new sheet, typically, has 1000 rows. The method in that case will return 1000.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet = spreadsheet.get_sheet_by_name('Sheet1')
max_row = sheet.get_max_row()Method to return the last column in sheet. this does not necessarily means a column with data. An empty new sheet, typically, has 26 columns (letter Z). The method in that case will return 26 even if the sheet has no data.
from sheetfu import SpreadsheetApp
sa = SpreadsheetApp('path/to/secret.json')
spreadsheet = sa.open_by_id(spreadsheet_id='<spreadsheet id>')
sheet = spreadsheet.get_sheet_by_name('Sheet1')
max_row = sheet.get_max_column()The Range object is where the magic happens. This is from this object that you will be able to get or set values, notes, colors, etc. This object implies working with two-dimensional lists (list of list) where an inside list represents a row.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_data_range()
values = data_range.get_values()
# values = [
# ['name', 'surname', 'age'],
# ['john', 'doe', 28],
# ['jane', 'doe', 27]
# ]The values are returned in the form of a 2D arrays. Empty cells will return empty strings.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_data_range()
notes = data_range.get_notes()Similar to get_values(), this will return a 2D list of the notes. When a cell does not contain a note, it returns an empty string.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_data_range()
backgrounds = data_range.get_backgrounds()
# [
# ['#ffffff', '#123456', '#000000'],
# ['#ffffff', '#123456', '#000000'],
# ['#ffffff', '#123456', '#000000']
#]The backgrounds colors are returned in the hexadecimal forms. An empty cell returns a white background (#ffffff).
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_data_range()
font_colors = data_range.get_font_colors()
# [
# ['#000000', '#000000', '#000000'],
# ['#000000', '#000000', '#000000'],
# ['#000000', '#000000', '#000000'],
#]The font colors are returned in the hexadecimal forms. An empty cell returns a black font (#000000).
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
values = [
['name', 'surname'],
['john', 'doe'],
['jane', 'doe'],
]
data_range.set_values(values)
data_range.commit()This will simply fill the values into the range A1:B3. A 2D list must be submitted, matching the range size. If it does not match, an error will be raised. Committing must be done or none of the changes will be sent to the spreadsheets.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
notes = [
['this is a note', 'this is a note'],
['', ''],
['', '']
]
data_range.set_backgrounds(backgrounds)
data_range.commit()This would set notes on the top 2 cells of the range. Empty strings means no notes to be submitted.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
backgrounds = [
['#0000FF', '#0000FF'],
['#0000FF', '#0000FF'],
['#0000FF', '#0000FF']
]
data_range.set_backgrounds(backgrounds)
data_range.commit()from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
font_colors = [
['#0000FF', '#0000FF'],
['#0000FF', '#0000FF'],
['#0000FF', '#0000FF']
]
data_range.set_font_colors(font_colors)
data_range.commit()from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.set_value('foo')
data_range.commit()This would set cells value to 'foo' in the whole range.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.set_note('this is a note')
data_range.commit()
This would put the note 'this is a note' on every cells within the range.from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.set_background('#0000FF')
data_range.commit()This would set the background of the whole range in blue.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.set_font_color('#0000FF')
data_range.commit()This would set the font colors of the whole range in blue.
This method is a key part of the API. It permits us to send all the changes we set at the same time, using the batch API of the google sheets v4 API.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.set_background('#000000') # black background
data_range.set_font_color('#0000FF') # blue font
values = [
['name', 'surname'],
['john', 'doe'],
['jane', 'doe'],
]
data_range.set_values(values)
# now pushing the changes
data_range.commit()When you set a change, nothing is actually sent to the spreadsheet. All the change setters instead are batched at the range level. The commit method sends every batched requests at once. This means being able to make as many change as you want while sending only one request to the google sheet api, giving a significant performance boost.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.get_row() # 1from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.get_column() # 1from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.get_max_row() # 3from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
data_range.get_max_column() # 2Get the range of a specific cell by giving its coordinates within the parent range. First row and first column starts at 1 (to keep it consistent with google sheet api).
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
cell_range = data_range.get_cell(row=1, column=1)Adds a dropdown with the given options on every cells within the range.
from sheetfu import SpreadsheetApp
ss = SpreadsheetApp('path/to/secret.json').open_by_id(spreadsheet_id='<spreadsheet id>')
data_range = ss.get_sheet_by_name('Sheet1').get_range_from_a1('A1:B3')
choices = [
'option1'
'option2'
]
data_range.add_dropdown(choices)