在 Python 中的谷歌电子表格 api v4 中获取工作表和最新工作表的列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38245714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get list of sheets and latest sheet in google spreadsheet api v4 in Python
提问by nexuscreator
I am trying to read and write values of different sheets in python 3 following the google official documentation. Though I am able to read values from certain sheets using range property in rangeName = 'Class Data!A2:E'in the code block mentioned below:
我正在尝试按照google 官方文档在 python 3 中读取和写入不同工作表的值。虽然我能够rangeName = 'Class Data!A2:E'在下面提到的代码块中使用 range 属性从某些工作表中读取值：
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
'version=v4')
service = discovery.build('sheets', 'v4', http=http,
discoveryServiceUrl=discoveryUrl)
spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
rangeName = 'Class Data!A2:E'
result = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get('values', [])
And I am trying to write values using the sample code from here:
requests.append({
'updateCells': {
'start': {'sheetId': 0, 'rowIndex': 0, 'columnIndex': 0},
'rows': [
{
'values': [
{
'userEnteredValue': {'numberValue': 1},
'userEnteredFormat': {'backgroundColor': {'red': 1}}
}, {
'userEnteredValue': {'numberValue': 2},
'userEnteredFormat': {'backgroundColor': {'blue': 1}}
}, {
'userEnteredValue': {'numberValue': 3},
'userEnteredFormat': {'backgroundColor': {'green': 1}}
}
]
}
],
'fields': 'userEnteredValue,userEnteredFormat.backgroundColor'
}
})
batchUpdateRequest = {'requests': requests}
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id,
body=batchUpdateRequest).execute()
The problem I am facing is that I am not able to retain latest sheet name or id from official documentation and as latest api revision is making random gid(we may not know what would be the sheet gid would be). Is there any way to refer list of sheets or spreadsheet latest revised sheet name or id using google sheet api v4?
我面临的问题是我无法从官方文档中保留最新的工作表名称或 ID，并且由于最新的 api 修订版正在制作随机 gid（我们可能不知道工作表 gid 是什么）。有没有办法使用google sheet api v4来引用工作表或电子表格最新修订的工作表名称或ID列表？
回答by user4426017
You can get a list of sheets by using the "get" method on spreadsheets:
您可以使用电子表格上的“get”方法获取工作表列表：
sheet_metadata = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
sheets = sheet_metadata.get('sheets', '')
title = sheets[0].get("properties", {}).get("title", "Sheet1")
sheet_id = sheets[0].get("properties", {}).get("sheetId", 0)
回答by WOUNDEDStevenJones
Nobody has asked about this on SO for the PHP library yet, but I just wanted to add this answer here as this is the first Google result for related questions.
没有人在 SO 上为 PHP 库询问过这个问题，但我只是想在这里添加这个答案，因为这是相关问题的第一个谷歌结果。
<?php
$sheets = array();
// ... load library and set up client ...
$service = new Google_Service_Sheets($client);
$response = $service->spreadsheets->get($spreadsheetId);
foreach($response->getSheets() as $s) {
$sheets[] = $s['properties']['title'];
}
return $sheets;
?>
回答by Matt
For those looking for a nodejs solution:
对于那些寻找 nodejs 解决方案的人：
const { google } = require("googleapis")
// Assuming auth has already been generated
const getSheets = async (auth, spreadsheetId) => {
const sheets = google.sheets({version: "v4", auth});
const result = (await sheets.spreadsheets.get({
spreadsheetId
})).data.sheets.map((sheet) => {
return sheet.properties.title
})
return result
}
Thisis what gets returned from await sheets.spreadsheets.get({ spreadsheetId })
这就是返回的内容await sheets.spreadsheets.get({ spreadsheetId })
Then, using this type, I map over the result of data.sheetsand return all of titles.
然后，使用这种类型，我映射结果data.sheets并返回所有标题。
It looks like it returns the titles in order of how they are on the actual google sheet.
看起来它按照标题在实际谷歌表上的顺序返回标题。
If you're trying to generate the auth token, then look here
如果您正在尝试生成身份验证令牌，请查看此处
回答by David Wainaina
For python.
对于蟒蛇。
service = build('sheets', 'v4', credentials=creds)
sheet_metadata = service.spreadsheets().get(spreadsheetId=SHEET_ID).execute()
properties = sheet_metadata.get('sheets')
for item in properties:
if item.get("properties").get('title') == 'SHEET_TITILE':
sheet_id = (item.get("properties").get('sheetId'))
print (sheet_id)
回答by JohnH
This is a C# method that returns the sheet id value of a specified sheet number and Spreadsheet id.
这是一个 C# 方法，它返回指定工作表编号和电子表格 ID 的工作表 ID 值。
This was made with the Google Sheets API v4.
这是使用 Google Sheets API v4 制作的。
private static Int32 GetSheetIdFromSheetNum(string sSpreadsheetId, Int32 iSheetNum, SheetsService gsService, ref string sTitle)
{ //Get the Sheet Id for the specified sheet number and Spreadsheet id.
Int32 iSheetId = -1;
Int32 iLoop = -1;
//Google.Apis.Sheets.v4.Data.Sheet gsSheet;
Google.Apis.Sheets.v4.Data.Spreadsheet gsSpreadsheet;
try
{
sTitle = string.Empty;
gsSpreadsheet = gsService.Spreadsheets.Get(sSpreadsheetId).Execute();
foreach (Sheet gsSheet in gsSpreadsheet.Sheets)
{
iLoop += 1;
if (iLoop != iSheetNum)
{
continue;
}
iSheetId = (gsSheet.Properties.SheetId).GetValueOrDefault(-1);
sTitle = gsSheet.Properties.Title;
break;
}
}
catch (Exception ex)
{
// Do error processing here.
}
return iSheetId;
} //GetSheetIdFromSheetNum
