Оптимизируйте свои подборки Сохраняйте и классифицируйте контент в соответствии со своими настройками.
Служба Advanced Sheets позволяет получить доступ к API Таблиц с помощью Apps Script. Подобно встроенной службе Apps Script для Google Таблиц , эта API позволяет скриптам читать, редактировать, форматировать и представлять данные в Google Таблицах. В большинстве случаев встроенная служба проще в использовании, но эта расширенная служба предоставляет несколько дополнительных функций.
Ссылка
Подробную информацию об этой службе см. в справочной документации по API Таблиц. Как и все расширенные службы в Apps Script, расширенная служба Таблиц использует те же объекты, методы и параметры, что и общедоступный API. Подробнее см. в разделе «Как определяются сигнатуры методов» .
В примере кода ниже используется версия API 4 ; это единственная версия API Таблиц, доступная в настоящее время в качестве расширенного сервиса в Apps Script.
Считать значения из диапазона
В следующем примере показано, как считывать значения данных из указанного диапазона на листе с помощью расширенного сервиса Таблиц. Это эквивалентно примеру рецепта « Считывание одного диапазона» .
/** * Read a range (A1:D5) of data values. Logs the values. * @param {string} spreadsheetId The spreadsheet ID to read from. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get */functionreadRange(spreadsheetId=yourspreadsheetId){try{constresponse=Sheets.Spreadsheets.Values.get(spreadsheetId,'Sheet1!A1:D5');if(response.values){console.log(response.values);return;}console.log('Failed to get range of values from spreadsheet');}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failed with error %s',e.message);}}
Запись значений в несколько диапазонов
В следующем примере показано, как записать данные в разные непересекающиеся диапазоны на листе с помощью одного запроса. Он эквивалентен примеру рецепта « Запись в несколько диапазонов» .
/** * Write to multiple, disjoint data ranges. * @param {string} spreadsheetId The spreadsheet ID to write to. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate */functionwriteToMultipleRanges(spreadsheetId=yourspreadsheetId){// Specify some values to write to the sheet.constcolumnAValues=[['Item','Wheel','Door','Engine']];constrowValues=[['Cost','Stocked','Ship Date'],['$20.50','4','3/1/2016']];constrequest={'valueInputOption':'USER_ENTERED','data':[{'range':'Sheet1!A1:A4','majorDimension':'COLUMNS','values':columnAValues},{'range':'Sheet1!B1:D2','majorDimension':'ROWS','values':rowValues}]};try{constresponse=Sheets.Spreadsheets.Values.batchUpdate(request,spreadsheetId);if(response){console.log(response);return;}console.log('response null');}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failed with error %s',e.message);}}
Добавить новый лист
В следующем примере показано, как создать новый лист с заданным размером и цветом вкладки. Он эквивалентен примеру « Добавить лист» .
/** * Add a new sheet with some properties. * @param {string} spreadsheetId The spreadsheet ID. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate */functionaddSheet(spreadsheetId=yourspreadsheetId){constrequests=[{'addSheet':{'properties':{'title':'Deposits','gridProperties':{'rowCount':20,'columnCount':12},'tabColor':{'red':1.0,'green':0.3,'blue':0.4}}}}];try{constresponse=Sheets.Spreadsheets.batchUpdate({'requests':requests},spreadsheetId);console.log('Created sheet with ID: '+response.replies[0].addSheet.properties.sheetId);}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failed with error %s',e.message);}}
Создать сводную таблицу
Следующий пример демонстрирует создание сводной таблицы на основе исходных данных. Он эквивалентен примеру « Добавление сводной таблицы» .
/** * Add a pivot table. * @param {string} spreadsheetId The spreadsheet ID to add the pivot table to. * @param {string} pivotSourceDataSheetId The sheet ID to get the data from. * @param {string} destinationSheetId The sheet ID to add the pivot table to. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate */functionaddPivotTable(spreadsheetId=yourspreadsheetId,pivotSourceDataSheetId=yourpivotSourceDataSheetId,destinationSheetId=yourdestinationSheetId){constrequests=[{'updateCells':{'rows':{'values':[{'pivotTable':{'source':{'sheetId':pivotSourceDataSheetId,'startRowIndex':0,'startColumnIndex':0,'endRowIndex':20,'endColumnIndex':7},'rows':[{'sourceColumnOffset':0,'showTotals':true,'sortOrder':'ASCENDING','valueBucket':{'buckets':[{'stringValue':'West'}]}},{'sourceColumnOffset':1,'showTotals':true,'sortOrder':'DESCENDING','valueBucket':{}}],'columns':[{'sourceColumnOffset':4,'sortOrder':'ASCENDING','showTotals':true,'valueBucket':{}}],'values':[{'summarizeFunction':'SUM','sourceColumnOffset':3}],'valueLayout':'HORIZONTAL'}}]},'start':{'sheetId':destinationSheetId,'rowIndex':49,'columnIndex':0},'fields':'pivotTable'}}];try{constresponse=Sheets.Spreadsheets.batchUpdate({'requests':requests},spreadsheetId);// The Pivot table will appear anchored to cell A50 of the destination sheet.}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failed with error %s',e.message);}}
[[["Прост для понимания","easyToUnderstand","thumb-up"],["Помог мне решить мою проблему","solvedMyProblem","thumb-up"],["Другое","otherUp","thumb-up"]],[["Отсутствует нужная мне информация","missingTheInformationINeed","thumb-down"],["Слишком сложен/слишком много шагов","tooComplicatedTooManySteps","thumb-down"],["Устарел","outOfDate","thumb-down"],["Проблема с переводом текста","translationIssue","thumb-down"],["Проблемы образцов/кода","samplesCodeIssue","thumb-down"],["Другое","otherDown","thumb-down"]],["Последнее обновление: 2025-08-04 UTC."],[[["\u003cp\u003eThe Advanced Sheets service allows Apps Script to interact with the Sheets API, enabling scripts to read, edit, format, and present data within Google Sheets.\u003c/p\u003e\n"],["\u003cp\u003eThis advanced service offers additional features beyond the built-in Google Sheets service, but requires enabling before use.\u003c/p\u003e\n"],["\u003cp\u003eThe service utilizes the same objects, methods, and parameters as the public Sheets API, mirroring its functionality within Apps Script.\u003c/p\u003e\n"],["\u003cp\u003eIt provides access to version 4 of the Sheets API, enabling actions such as reading and writing data, adding sheets, and creating pivot tables.\u003c/p\u003e\n"]]],[],null,["The Advanced Sheets service lets you access the [Sheets API](/sheets/api) using\nApps Script. Much like Apps Script's [built-in Google Sheets API service](/apps-script/reference/spreadsheet),\nthis API allows scripts to read, edit, format and present data in Google Sheets.\nIn most cases, the built-in service is easier to use, but this\nadvanced service provides a few extra features.\n| This is an advanced service that must be [enabled before use](/apps-script/guides/services/advanced).\n\nReference\n\nFor detailed information on this service, see the\n[reference documentation](/sheets/api/reference/rest) for the Sheets API.\nLike all advanced services in Apps Script, the advanced Sheets service uses the\nsame objects, methods, and parameters as the public API. For more information, see [How method signatures are determined](/apps-script/guides/services/advanced#how_method_signatures_are_determined).\n\nTo report issues and find other support, see the\n[Sheets support guide](/sheets/api/support).\n\nSample code\n\nThe sample code below uses [version 4](/sheets/api/reference/rest) of the API;\nthis is the only version of the Sheets API currently available as an\nadvanced service in Apps Script.\n\nRead values from a range\n\nThe following example demonstrates how to read data values from a specified\nrange in a sheet with the Sheets advanced service. It is equivalent to the\n[Read a single range](/sheets/api/samples/reading#read_a_single_range)\nrecipe sample. \nadvanced/sheets.gs \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/advanced/sheets.gs) \n\n```gosu\n/**\n * Read a range (A1:D5) of data values. Logs the values.\n * @param {string} spreadsheetId The spreadsheet ID to read from.\n * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get\n */\nfunction readRange(spreadsheetId = yourspreadsheetId) {\n try {\n const response = Sheets.Spreadsheets.Values.get(spreadsheetId, 'Sheet1!A1:D5');\n if (response.values) {\n console.log(response.values);\n return;\n }\n console.log('Failed to get range of values from spreadsheet');\n } catch (e) {\n // TODO (developer) - Handle exception\n console.log('Failed with error %s', e.message);\n }\n}\n```\n\nWrite values to multiple ranges\n\nThe following example demonstrates how to write data to different, disjoint\nranges in a sheet with one request. It is equivalent to the\n[Write to multiple ranges](/sheets/api/samples/writing#write_to_multiple_ranges)\nrecipe sample. \nadvanced/sheets.gs \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/advanced/sheets.gs) \n\n```gosu\n/**\n * Write to multiple, disjoint data ranges.\n * @param {string} spreadsheetId The spreadsheet ID to write to.\n * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate\n */\nfunction writeToMultipleRanges(spreadsheetId = yourspreadsheetId) {\n // Specify some values to write to the sheet.\n const columnAValues = [\n ['Item', 'Wheel', 'Door', 'Engine']\n ];\n const rowValues = [\n ['Cost', 'Stocked', 'Ship Date'],\n ['$20.50', '4', '3/1/2016']\n ];\n\n const request = {\n 'valueInputOption': 'USER_ENTERED',\n 'data': [\n {\n 'range': 'Sheet1!A1:A4',\n 'majorDimension': 'COLUMNS',\n 'values': columnAValues\n },\n {\n 'range': 'Sheet1!B1:D2',\n 'majorDimension': 'ROWS',\n 'values': rowValues\n }\n ]\n };\n try {\n const response = Sheets.Spreadsheets.Values.batchUpdate(request, spreadsheetId);\n if (response) {\n console.log(response);\n return;\n }\n console.log('response null');\n } catch (e) {\n // TODO (developer) - Handle exception\n console.log('Failed with error %s', e.message);\n }\n}\n```\n\nAdd a new sheet\n\nThe following example demonstrates how to create a new sheet with specific\nsize and tab color. It is equivalent to the\n[Add a sheet](/sheets/api/samples/sheet#add_a_sheet) recipe sample. \nadvanced/sheets.gs \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/advanced/sheets.gs) \n\n```gosu\n/**\n * Add a new sheet with some properties.\n * @param {string} spreadsheetId The spreadsheet ID.\n * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate\n */\nfunction addSheet(spreadsheetId = yourspreadsheetId) {\n const requests = [{\n 'addSheet': {\n 'properties': {\n 'title': 'Deposits',\n 'gridProperties': {\n 'rowCount': 20,\n 'columnCount': 12\n },\n 'tabColor': {\n 'red': 1.0,\n 'green': 0.3,\n 'blue': 0.4\n }\n }\n }\n }];\n try {\n const response =\n Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);\n console.log('Created sheet with ID: ' +\n response.replies[0].addSheet.properties.sheetId);\n } catch (e) {\n // TODO (developer) - Handle exception\n console.log('Failed with error %s', e.message);\n }\n}\n```\n\nCreate a pivot table\n\nThe following example demonstrates how to create a pivot table from source data.\nIt is equivalent to the [Add a pivot table](/sheets/api/samples/pivot-tables#add_a_pivot_table)\nrecipe sample. \nadvanced/sheets.gs \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/advanced/sheets.gs) \n\n```gosu\n/**\n * Add a pivot table.\n * @param {string} spreadsheetId The spreadsheet ID to add the pivot table to.\n * @param {string} pivotSourceDataSheetId The sheet ID to get the data from.\n * @param {string} destinationSheetId The sheet ID to add the pivot table to.\n * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate\n */\nfunction addPivotTable(\n spreadsheetId = yourspreadsheetId,\n pivotSourceDataSheetId= yourpivotSourceDataSheetId,\n destinationSheetId= yourdestinationSheetId) {\n const requests = [{\n 'updateCells': {\n 'rows': {\n 'values': [\n {\n 'pivotTable': {\n 'source': {\n 'sheetId': pivotSourceDataSheetId,\n 'startRowIndex': 0,\n 'startColumnIndex': 0,\n 'endRowIndex': 20,\n 'endColumnIndex': 7\n },\n 'rows': [\n {\n 'sourceColumnOffset': 0,\n 'showTotals': true,\n 'sortOrder': 'ASCENDING',\n 'valueBucket': {\n 'buckets': [\n {\n 'stringValue': 'West'\n }\n ]\n }\n },\n {\n 'sourceColumnOffset': 1,\n 'showTotals': true,\n 'sortOrder': 'DESCENDING',\n 'valueBucket': {}\n }\n ],\n 'columns': [\n {\n 'sourceColumnOffset': 4,\n 'sortOrder': 'ASCENDING',\n 'showTotals': true,\n 'valueBucket': {}\n }\n ],\n 'values': [\n {\n 'summarizeFunction': 'SUM',\n 'sourceColumnOffset': 3\n }\n ],\n 'valueLayout': 'HORIZONTAL'\n }\n }\n ]\n },\n 'start': {\n 'sheetId': destinationSheetId,\n 'rowIndex': 49,\n 'columnIndex': 0\n },\n 'fields': 'pivotTable'\n }\n }];\n try {\n const response = Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);\n // The Pivot table will appear anchored to cell A50 of the destination sheet.\n } catch (e) {\n // TODO (developer) - Handle exception\n console.log('Failed with error %s', e.message);\n }\n}\n```"]]
