-
Notifications
You must be signed in to change notification settings - Fork 88
Users Spreadsheets
- API documentation
- Query documentation
- Definitions
- JSON data quoting
- Create spreadsheets
- Update spreadsheets
- Display information about spreadsheets
- Clear values in a spreadsheet
- Display values in a spreadsheet
- Append/update values in a spreadsheet
- Extended Examples with show sheetrange
- Extended Examples with print sheetrange
- Repair an uneditable sheet within a spreadsheet
- https://developers.google.com/sheets/api/reference/rest/
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append#InsertDataOption
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchClear
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet
- https://developers.google.com/sheets/api/reference/rest/v4/Dimension
- https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption
- https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption
- https://developers.google.com/drive/api/v3/search-files
- https://developers.google.com/drive/api/v3/ref-search-terms
<DriveFileParentAttribute> ::=
(parentid <DriveFolderID>)|
(parentname <DriveFolderName>)|
(anyownerparentname <DriveFolderName>)|
(teamdriveparentid <DriveFolderID>)|
(teamdriveparent <SharedDriveName>)|
(teamdriveparentid <SharedDriveID> teamdriveparentname <DriveFolderName>)|
(teamdriveparent <SharedDriveName> teamdriveparentname <DriveFolderName>))|
(teamdriveparentid <DriveFolderID>)|(teamdriveparent <SharedDriveName>)|
(teamdriveparentid <SharedDriveID> teamdriveparentname <DriveFolderName>)|
(teamdriveparent <SharedDriveName> teamdriveparentname <DriveFolderName>)
- The following right-hand side characters are literal:
{}[],'": - The following right-hand side characters are part of the meta-syntax:
<>()*|
<SpreadsheetJSONCreateRequest> ::=
'{"properties": {<SpreadsheetProperties>}, "sheets": [{<Sheet>}(,{<Sheet>})*],
"namedRanges": [{<NamedRange>}(,{<NamedRange>})*],
"developerMetadata": [{<DeveloperMetadata>}(,{DeveloperMetadata>})]}'
<SpreadsheetJSONUpdateRequest> ::=
'{"requests": [{<Request>}(,{<Request>})], "includeSpreadsheetInResponse": true|false,
"responseRanges": ["<SpreadsheetRange>"(,"<SpreadsheetRange>")],
"responseIncludeGridData": true|false}
<SpreadsheetField> ::=
developermetadata|
namedranges|
properties|
sheets|
spreadsheetid|
spreadsheeturl
<SpreadsheetFieldList> ::= "<SpreadsheetField>(,<SpreadsheetField>)*"
<SpreadsheetSheetsField> ::=
bandedranges|
basicfilter|
charts|
columngroups|
conditionalformats|
data|
developermetadata|
filterviews|
merges|
properties|
protectedranges|
rowgroups|
slicers
<SpreadsheetRange> ::= <String>
<SpreadsheetRangeList> ::= "'<SpreadsheetRange>'(,'<SpreadsheetRange>')*"
<SpreadsheetValue> ::= "<String>"|<Number>|true|false
<SpreadsheetValueList> ::= "<SpreadsheetValue>(,<SpreadsheetValue>)*"
<SpreadsheetJSONRangeValues> ::=
'{"range": <SpreadsheetRange>, "values": [<SpreadsheetValueList>(,<SpreadsheetValueList>)*],
"majorDimension": "ROWS|COLUMNS"}'
<SpreadsheetJSONRangeValuesList> ::=
'[<SpreadsheetJSONRangeValues>(,<SpreadsheetJSONRangeValues>)*]'
When entering JSON data from the command line, different quoting rules apply based on OS.
In all cases, surround entire argument in single quotes.
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson valuerangesonly
gam user [email protected] update sheetranges drivefilename TestSheet json '[{"range": "A1:C1", "values": [[1, 2, 3]], "majorDimension": "ROWS"}, {"range": "A3:C3", "values": [["10/01/2017 10:30:00", true, 6]], "majorDimension": "ROWS"}]' userentered includevaluesinresponse
For JSON data without double quotes, surround entire argument in double quotes.
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range "Sheet1!A1:B2" formatjson valuerangesonly
For JSON data with double quotes, surround entire argument in single quotes; guard double quotes with backslah.
gam user [email protected] update sheetranges drivefilename TestSheet json '[{\"range\": \"A1:C1\", \"values\": [[1, 2, 3]], \"majorDimension\": \"ROWS\"}, {\"range\": \"A3:C3\", \"values\": [[\"10/01/2017 10:30:00\", true, 6]], \"majorDimension\": \"ROWS\"}]' userentered includevaluesinresponse
For JSON data without double quotes, surround entire argument in double quotes.
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range "Sheet1!A1:B2" formatjson valuerangesonly
For JSON data with double quotes, surround entire argument in double quotes; guard double quotes with backslah.
gam user [email protected] update sheetranges drivefilename TestSheet json "[{\"range\": \"A1:C1\", \"values\": [[1, 2, 3]], \"majorDimension\": \"ROWS\"}, {\"range\": \"A3:C3\", \"values\": [[\"10/01/2017 10:30:00\", true, 6]], \"majorDimension\": \"ROWS\"}]" userentered includevaluesinresponse
When entering data from a JSON file, no special quoting is required.
File Sheet.json contains:
[{"range": "A1:C1", "values": [[1, 2, 3]], "majorDimension": "ROWS"}, {"range": "A3:C3", "values": [["10/01/2017 10:30:00", true, 6]], "majorDimension": "ROWS"}]
gam user [email protected] update sheetranges drivefilename TestSheet json file Sheet.json
Surround data in single quotes.
CSV file Sheet.csv contains:
User,spreadsheetId,JSON
[email protected],1MOq6umgWSM7NF8-CQ-Aj3_n1DIu_GvyCcuLxxxxxx,'[{"range": "Sheet1!A1:C1", "values": [["1", "2", "3"]], "majorDimension": "ROWS"}, {"range": "Sheet1!A3:C3", "values": [["10/01/2017 10:30:00", true, "6"]], "majorDimension": "ROWS"}]'
gam csv Sheet.csv quotechar "'" gam user "~User" update sheetranges "~spreadsheetId" json "~JSON" userentered includevaluesinresponse
gam <UserTypeEntity> create|add sheet
((json [charset <Charset>] <SpreadsheetJSONCreateRequest>) |
(json file <FileName> [charset <Charset>]))
[<DriveFileParentAttribute>]
[formatjson] [returnidonly]
The JSON data can be read from a command line argument or a file.
The Google Sheets API creates the spreadheet in the user's root folder; use the following options to have Gam assign the spreadsheet to a different location. If the assignment fails, the spreadsheet is left in the root folder.
-
parentid <DriveFolderID>- Folder ID. -
parentname <DriveFolderName>- Folder name; the folder must be owned by<UserTypeEntity>. -
anyownerparentname <DriveFolderName>- Folder name; the folder can be owned by any user,<UserTypeEntity>must be able to write to the folder. -
teamdriveparentid <DriveFolderID>- Shared Drive folder ID; when used alone, this indicates a specfic Shared Drive folder. -
teamdriveparent <SharedDriveName>- Shared Drive name; when used alone, this indicates the root level of the Shared Drive. -
teamdriveparentid <SharedDriveID> teamdriveparentname <DriveFolderName>- A Shared Drive ID and a folder name on that Shared Drive. -
teamdriveparent <SharedDriveName> teamdriveparentname <DriveFolderName>- A Shared Drive name and a folder name on that Shared Drive. - If none of the parent options are specified, the parent folder is the root folder.
The output is formatted for human readability; use the following options to produce JSON output
-
formatjson- Display output in JSON format for program parsing. -
returnidonly- Display just the file ID of the created spreadsheet file as output
Create a basic sheet.
File Sheet.json contains:
{"properties": {"title": "Gam Sheet"}, "sheets": [{"properties": {"sheetId": 0, "title": "Gam Tab", "sheetType": "GRID"}}]}
gam user [email protected] create sheet json file Sheet.json
gam <UserTypeEntity> update sheet <DriveFileEntity>
((json [charset <Charset>] <SpreadsheetJSONUpdateRequest>) |
(json file <FileName> [charset <Charset>]))
[formatjson]
The JSON data can be read from a command line argument or a file. On the command line, the JSON data is enclosed in single quotes; these should not be present when the JSON data is read from a file.
The output is formatted for human readability. Use the following option to produce JSON output for program parsing.
-
formatjson- Display output in JSON format.
Add a new tab/sheet to a spreadsheet.
File Sheet.json contains:
{"requests": [{"addSheet": {"properties": {"title": "New Sheet", "sheetType": "GRID"}}}]}
gam user [email protected] update sheet <DriveFileItem> json file Sheet.json
Delete a tab/sheet from a spreadsheet.
Get the sheet IDs.
gam user [email protected] info sheet <DriveFileItem> fields sheets
Get the desired sheetId from the output.
File Sheet.json contains:
{"requests": [{"deleteSheet": {"sheetId": 1234567890}}]}
gam user [email protected] update sheet <DriveFileItem> json file Sheet.json
Rename a tab/sheet in a spreadsheet.
Get the sheet IDs.
gam user [email protected] info sheet <DriveFileItem> fields sheets
Get the desired sheetId from the output.
File Sheet.json contains:
{"requests": [{"updateSheetProperties": {"properties": {"sheetId": 1234567890, "title": "New Title"}, "fields": "title"}}]}
gam user [email protected] update sheet <DriveFileItem> json file Sheet.json
Delete a column from a tab in a spreadsheet.
Get the sheet IDs.
gam user [email protected] info sheet <DriveFileItem> fields sheets
Get the desired sheetId from the output.
Columns are numbered starting from 0 at the left; specify the starting column and the ending column + 1.
In this example, column B is being deleted.
File Sheet.json contains:
{"requests": [{"deleteRange": {"range": {"sheetId": 1234567890, "startColumnIndex": 1, "endColumnIndex": 2}, "shiftDimension": "COLUMNS"}}]}
gam user [email protected] update sheet <DriveFileItem> json file Sheet.json
gam <UserTypeEntity> info|show sheet <DriveFileEntity>
[fields <SpreadsheetFieldList>] [sheetsfields <SpreadsheetSheetsFieldList>]
(range <SpreadsheetRange>)* (rangelist <SpreadsheetRangeList>)*
[includegriddata [<Boolean>]] [shownames]
[formatjson]
By default, the Sheets API does not return the sheet file name, use the shownames option to have GAM
make an additional API call to get and display the sheet file name.
The output is formatted for human readability. Use the following option to produce JSON output for program parsing.
-
formatjson- Display output in JSON format.
gam <UserTypeEntity> print sheet <DriveFileEntity> [todrive <ToDriveAttribute>*]
[fields <SpreadsheetFieldList>] [sheetsfields <SpreadsheetSheetsFieldList>]
(range <SpreadsheetRange>)* (rangelist <SpreadsheetRangeList>)*
[includegriddata [<Boolean>]] [shownames]
[formatjson [quotechar <Character>]]
By default, the Sheets API does not return the sheet file name, use the shownames option to have GAM
make an additional API call to get and display the sheet file name.
By default, when writing CSV files, Gam uses a quote character of double quote ". The quote character is used to enclose columns that contain
the quote character itself, the column delimiter (comma by default) and new-line characters. Any quote characters within the column are doubled.
When using the formatjson option, double quotes are used extensively in the data resulting in hard to read/process output.
The quotechar <Character> option allows you to choose an alternate quote character, single quote for instance, that makes for readable/processable output.
quotechar defaults to gam.cfg/csv_output_quote_char. When uploading CSV files to Google, double quote " should be used.
gam <UserTypeEntity> clear sheetrange <DriveFileEntity>
(range <SpreadsheetRange>)+ (rangelist <SpreadsheetRangeList>)*
[formatjson]
Multiple ranges can be specified.
With clear, the output is formatted for human readability. Use the following option to produce JSON output for program parsing.
-
formatjson- Display output in JSON format.
gam user [email protected] clear sheetranges drivefilename TestSheet range A4:C5 range A9:C10
gam <UserTypeEntity> print sheetrange <DriveFileEntity> [todrive <ToDriveAttribute>*]
(range <SpreadsheetRange>)* (rangelist <SpreadsheetRangeList>)*
[rows|columns] [formula|formattedvalue|unformattedvalue]
[serialnumber|formattedstring]
[formatjson [quotechar <Character>] [valuerangesonly [<Boolean>]]]
gam <UserTypeEntity> show sheetrange <DriveFileEntity>
(range <SpreadsheetRange>)* (rangelist <SpreadsheetRangeList>)*
[rows|columns] [formula|formattedvalue|unformattedvalue]
[serialnumber|formattedstring]
[formatjson [valuerangesonly [<Boolean>]]]
Multiple ranges can be specified.
Specify how the values are displayed; the default is rows.
-
rows- Display rows of data -
columns- Display columns of data
Specify how values should be rendered in the output; the default is formattedvalue.
-
formula- Values will not be calculated; the reply will include the formulas. -
formattedvalue- Values will be calculated and formatted in the reply according to the cell's formatting. -
unformattedvalue- Values will be calculated, but not formatted in the reply.
Specify how date and time values should be rendered in the output; the default is formattedstring.
-
serialnumber- Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3. -
formattedstring- Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which is dependent on the spreadsheet locale).
With show, the values output is formatted for human readability. With print, the values output is expanded into multiple columns.
-
formatjson- Display values output in JSON format.
By default, when writing CSV files, Gam uses a quote character of double quote ". The quote character is used to enclose columns that contain
the quote character itself, the column delimiter (comma by default) and new-line characters. Any quote characters within the column are doubled.
When using the formatjson option, double quotes are used extensively in the data resulting in hard to read/process output.
The quotechar <Character> option allows you to choose an alternate quote character, single quote for instance, that makes for readable/processable output.
quotechar defaults to gam.cfg/csv_output_quote_char. When uploading CSV files to Google, double quote " should be used.
The valuerangesonly option used in conjunction with formatjson limits the display to just the valueRanges data.
This will make it simpler to capture the sheetrange data, modify it, and update the sheet with the modified data.
For Windows, see JSON data quoting
gam user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson
Getting all Drive Files/Folders that match query ('me' in owners and name = 'TestSheet') for [email protected]
Got 1 Drive File/Folder that matched query ('me' in owners and name = 'TestSheet') for [email protected]...
{"User": "[email protected]", "spreadsheetId": "111-Ca4obeBiojvhWFfMH12lHz71GTwBmJGbL-KnYzzz", "JSON": {"valueRanges": [{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]}}
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson
gam user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson valuerangesonly
Getting all Drive Files/Folders that match query ('me' in owners and name = 'TestSheet') for [email protected]
Got 1 Drive File/Folder that matched query ('me' in owners and name = 'TestSheet') for [email protected]...
[{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson valuerangesonly
gam user [email protected] print sheetrange name "TestSheet" range 'Sheet1!A1:B2' formatjson quotechar "'"
Getting all Drive Files/Folders that match query ('me' in owners and name = 'TestSheet') for [email protected]
Got 1 Drive File/Folder that matched query ('me' in owners and name = 'TestSheet') for [email protected]...
User,spreadsheetId,JSON
[email protected],1uK-Ca4obeBiojvhWFfMH12lHz71GTwBmJGbL-KnY2tM,'{"valueRanges": [{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]}'
gam redirect csv SheetData.csv user [email protected] print sheetrange name "TestSheet" range 'Sheet1!A1:B2' formatjson quotechar "'"
gam user [email protected] print sheetrange name "TestSheet" range 'Sheet1!A1:B2' formatjson quotechar "'" valuerangesonly
Getting all Drive Files/Folders that match query ('me' in owners and name = 'TestSheet') for [email protected]
Got 1 Drive File/Folder that matched query ('me' in owners and name = 'TestSheet') for [email protected]...
JSON
'[{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]'
gam redirect csv SheetData.csv user [email protected] print sheetrange name "TestSheet" range 'Sheet1!A1:B2' formatjson quotechar "'" valuerangesonly
gam <UserTypeEntity> append sheetrange <DriveFileEntity>
((json [charset <Charset>] <SpreadsheetJSONRangeValues>|<SpreadsheetJSONRangeValuesList>) |
(json file <FileName> [charset <Charset>]))
[overwrite|insertrows]
[raw|userentered] [formula|formattedvalue|unformattedvalue] [serialnumber|formattedstring]
[includevaluesinresponse [<Boolean>]] [formatjson]
gam <UserTypeEntity> update sheetrange <DriveFileEntity>
((json [charset <Charset>] <SpreadsheetJSONRangeValues>|<SpreadsheetJSONRangeValuesList>)+
(json file <FileName> [charset <Charset>])+)
[raw|userentered] [formula|formattedvalue|unformattedvalue] [serialnumber|formattedstring]
[includevaluesinresponse [<Boolean>]] [formatjson]
-
append- Only one range can be specified. -
update- Multiple ranges can be specified.
The JSON data can be read from a command line argument or a file.
Specify how data is to be appended to the spreadsheet; the default is insertrows.
-
overwrite- The new data overwrites existing data in the areas it is written. -
insertrows- Rows are inserted for the new data.
Specify how the values in <SpreadsheetValueList> are to be parsed; the default is userentered.
-
raw- The values the user has entered will not be parsed and will be stored as-is. -
userentered- The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.
Specify whether the response should include the values of the cells that were appended/updated; the default is false.
includevaluesinresponse [<Boolean>]
Specify how values should be rendered in the output; the default is formattedvalue.
-
formula- Values will not be calculated; the reply will include the formulas. -
formattedvalue- Values will be calculated and formatted in the reply according to the cell's formatting. -
unformattedvalue- Values will be calculated, but not formatted in the reply.
Specify how date and time values should be rendered in the output; the default is formattedstring.
-
serialnumber- Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3. -
formattedstring- Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which is dependent on the spreadsheet locale).
With append and update, the output is formatted for human readability. Use the following option to produce JSON output for program parsing.
-
formatjson- Display output in JSON format.
For Windows, see JSON data quoting
Update a spreadhseet with JSON data from the command line. All of the data must be on one line.
gam user [email protected] update sheetrange name TestSheet json '[{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]'
Update a spreadsheet with JSON data from a file.
The JSON parser is OK with the values being on separate lines in the file, as long as the syntax is correct. Do note that the JSON file contains information on where the data is to be updated, referenced with both sheet name and range.
more SheetData.json
[{"range": "Sheet1!C2:E3", "majorDimension": "ROWS", "values":
[["0102", "0304", "AB"],
["0506", "0708", "CD"]]},
{"range": "Sheet2!B9:C10", "majorDimension": "ROWS", "values":
[["0910", "1112"],
["1314", "1516"]]}]
gam user [email protected] update sheetrange name TestSheet json file SheetData.json
For Windows, see JSON data quoting
Increment values in a sheet with local data; repeat these steps as required.
- Get the current sheet data with
show sheetrange
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson valuerangesonly
more SheetData.json
[{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]
- Edit SheetData.json
- Update the sheet data
gam user [email protected] update sheetrange name TestSheet json file SheetData.json
Replace values in a sheet with local data.
- Get the current sheet data; perform this step once.
gam redirect stdout SheetData.json user [email protected] show sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson valuerangesonly
Replace values in a sheet with local data; repeat these steps as required.
- Edit SheetData.json
- Update the sheet data
gam user [email protected] update sheetrange name TestSheet json file SheetData.json
For Windows, see JSON data quoting
Increment values in a sheet with local data; repeat these steps as required.
- Get the current sheet data
gam redirect csv SheetData.csv user [email protected] print sheetrange name TestSheet range 'Sheet1!A1:B2' formatjson quotechar "'" valuerangesonly
more SheetData.csv
JSON
'[{"range": 'Sheet1!A1:B2', "majorDimension": "ROWS", "values": [["11", "12"], ["21", "22"]]}]'
- Edit SheetData.csv
- Update the sheet data
gam csv SheetData.csv quotechar "'" gam user [email protected] update sheetrange name TestSheet json "~JSON"
Update values in multiple users/sheets.
- Users/sheets to update
more SheetsToUpdate.csv
User,SpreadsheetName,Rangelist
[email protected],SimpleSheet,"'Sheet1!A1:C1','Sheet1!C3'"
[email protected],TestSheet,'Sheet1!A1:B2'
- Get the current sheet data
gam redirect csv SheetData.csv multiprocess csv SheetsToUpdate.csv gam user "~User" print sheetrange name "~SpreadsheetName" rangelist "~Rangelist" formatjson quotechar "'"
- Edit SheetData.csv
- Update the sheet data
gam csv SheetData.csv quotechar "'" gam user "~User" update sheetrange "~spreadsheetId" json "~JSON"
Identify uneditable sheet; there is no editors field.
$ gam user [email protected] info sheet 1234-y9d0nbckO_cnb3xyZhsIh0Hxd9WaqpGPBwxyz fields sheets sheetsfields protectedranges
User: [email protected], Show Info 1 Spreadsheet
Spreadsheet: 1234-y9d0nbckO_cnb3xyZhsIh0Hxd9WaqpGPBwxyz
...
Sheet: Test (5/5)
properties:
gridProperties:
columnCount: 76
frozenColumnCount: 1
hideGridlines: True
rowCount: 60
index: 49
sheetId: 420957925
sheetType: GRID
title: Test
protectedRanges:
protectedRangeId: 2117059324
range:
sheetId: 420957925
Make a JSON file that will add the editors field.
$ cat RepairSheet.json
{"requests": [
{"updateProtectedRange": {"protectedRange": {"protectedRangeId": 2117059324, "editors": {"users": ["[email protected]]}}, "fields": "editors"}}
]}
Apply the repair update.
$ gam user [email protected] update sheet 1234-y9d0nbckO_cnb3xyZhsIh0Hxd9WaqpGPBwxyz json file RepairSheet.json
User: [email protected], Update 1 Spreadsheet
User: [email protected], Spreadsheet: 1234-y9d0nbckO_cnb3xyZhsIh0Hxd9WaqpGPBwxyz, Updated
replies:
Verify the repair.
$ gam user [email protected] info sheet 1234-y9d0nbckO_cnb3xyZhsIh0Hxd9WaqpGPBwxyz fields sheets sheetsfields protectedranges
User: [email protected], Show Info 1 Spreadsheet
Spreadsheet: 1234-y9d0nbckO_cnb3xyZhsIh0Hxd9WaqpGPBwxyz
...
Sheet: Test (5/5)
properties:
gridProperties:
columnCount: 76
frozenColumnCount: 1
hideGridlines: True
rowCount: 60
index: 49
sheetId: 420957925
sheetType: GRID
title: Test
protectedRanges:
protectedRangeId: 2117059324
editors:
users:
[email protected]
range:
sheetId: 420957925
Need more help? Ask on the GAM Discussion Group
Update History
Installation
- How to Install GAM7
- How to Uograde GAMADV-XTD3 to GAM7
- How to Upgrade Legacy GAM to GAM7
- How to Update GAM7
- Install GAM as Python Library
- GAM7 on Chrome OS Devices
- GAM7 on Android Devices
- Google Network Addresses
- HTTPS Proxy
- SSL Root CA Certificates
- How to Uninstall GAM7
Configuration
- Authorization
- GAM Configuration
- Running GAM7 securely on a Google Compute Engine
- Using GAM7 with a delegated admin service account
- Using GAM7 with a YubiKey
Notes and Information
- Upgrade Benefits
- Questions? Visit the GAM Discussion Forum
- GAM Public Chat Room
- Scripts
- Other Resources
- Drive REST API v3
- BNF Syntax
- GAM Return Codes
- Python Regular Expressions
- Rclone
Definitions
Command Processing
- Bulk Processing
- Command Line Parsing
- Command Logging and Progress
- Command data from Google Docs/Sheets/Storage
- CSV Special Characters
- CSV Input Filtering
- CSV Output Filtering
- Meta Commands and File Redirection
- Permission matches
- Tag Replace
- Todrive
Collections
Client Access
- Addresses
- Administrators
- Alert Center
- Aliases
- Calendars
- Calendars - Access
- Calendars - Events
- Chrome Auto Update Expiration Counts
- Chrome Browser Cloud Management
- Chrome Device Needs Attention Counts
- Chrome Installed Apps
- Chrome Policies
- Chrome Printers
- Chrome Profile Management
- Chrome Version Counts
- Chrome Version History
- ChromeOS Devices
- Classroom - Courses
- Classroom - Guardians
- Classroom - Invitations
- Classroom - Membership
- Cloud Channel
- Cloud Identity Devices
- Cloud Identity Groups
- Cloud Identity Groups - Membership
- Cloud Identity Policies
- Cloud Storage
- Context Aware Access Levels
- Customer
- Domains
- Domains - Verification
- Domain People - Contacts & Profiles
- Domain Shared Contacts - Global Address List
- Email Audit Monitor
- Find File Owner
- Google Data Transfers
- Groups
- Groups - Membership
- Inbound SSO
- Licenses
- Mobile Devices
- Organizational Units
- Reports
- Reseller
- Resources
- Send Email
- Schemas
- Shared Drives
- Sites
- Users
- Unmanaged Accounts
- Users - Signout and Turn off 2-Step Verification
- Vault - Takeout
- Version and Help
Special Service Account Access
Service Account Access
- Users - Analytics Admin
- Users - Application Specific Passwords
- Users - Backup Verification Codes
- Users - Calendars
- Users - Calendars - Access
- Users - Calendars - Events
- Users - Chat
- Users - Classification Labels
- Users - Classroom - Profile
- Users - Deprovision
- Users - Contacts
- Users - Contacts - Delegates
- Users - Drive - File Selection
- Users - Drive - Activity/Settings
- Users - Drive - Cleanup
- Users - Drive - Comments
- Users - Drive - Copy/Move
- Users - Drive - Files-Display
- Users - Drive - Files-Manage
- Users - Drive - Orphans
- Users - Drive - Ownership
- Users - Drive - Permissions
- Users - Drive - Query
- Users - Drive - Revisions
- Users - Drive - Shortcuts
- Users - Drive - Transfer
- Users - Forms
- Users - Gmail - Client Side Encryption
- Users - Gmail - Delegates
- Users - Gmail - Filters
- Users - Gmail - Forwarding
- Users - Gmail - Labels
- Users - Gmail - Messages/Threads
- Users - Gmail - Profile
- Users - Gmail - S/MIME
- Users - Gmail - SendAs/Signature/Vacation
- Users - Gmail - Settings
- Users - Group Membership
- Users - Keep
- Users - Looker Studio
- Users - Meet
- Users - Classroom - Profile
- Users - People - Contacts & Profiles
- Users - Photo
- Users - Profile Sharing
- Users - Shared Drives
- Users - Spreadsheets
- Users - Tasks
- Users - Tokens
- Users - YouTube