Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.
Le service Sheets avancé vous permet d'accéder à l'API Sheets à l'aide d'Apps Script. Tout comme le service d'API Google Sheets intégré à Apps Script, cette API permet aux scripts de lire, de modifier, de mettre en forme et de présenter des données dans Google Sheets. Dans la plupart des cas, le service intégré est plus facile à utiliser, mais ce service avancé offre quelques fonctionnalités supplémentaires.
Référence
Pour en savoir plus sur ce service, consultez la documentation de référence de l'API Sheets. Comme tous les services avancés d'Apps Script, le service Sheets avancé utilise les mêmes objets, méthodes et paramètres que l'API publique. Pour en savoir plus, consultez Déterminer les signatures de méthode.
L'exemple de code ci-dessous utilise la version 4 de l'API. Il s'agit de la seule version de l'API Sheets actuellement disponible en tant que service avancé dans Apps Script.
Lire les valeurs d'une plage
L'exemple suivant montre comment lire les valeurs de données d'une plage spécifiée dans une feuille à l'aide du service avancé Sheets. Il équivaut à l'exemple de recette Lire une seule plage.
/** * 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);}}
Écrire des valeurs dans plusieurs plages
L'exemple suivant montre comment écrire des données dans différentes plages disjointes d'une feuille avec une seule requête. Il équivaut à l'exemple de recette Écrire dans plusieurs plages.
/** * 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);}}
Ajouter une feuille
L'exemple suivant montre comment créer une feuille avec une taille et une couleur d'onglet spécifiques. Il équivaut à l'exemple de recette Ajouter une feuille.
/** * 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);}}
Créer un tableau croisé dynamique
L'exemple suivant montre comment créer un tableau croisé dynamique à partir de données sources. Il équivaut à l'exemple de recette Ajouter un tableau croisé dynamique.
/** * 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);}}
Sauf indication contraire, le contenu de cette page est régi par une licence Creative Commons Attribution 4.0, et les échantillons de code sont régis par une licence Apache 2.0. Pour en savoir plus, consultez les Règles du site Google Developers. Java est une marque déposée d'Oracle et/ou de ses sociétés affiliées.
Dernière mise à jour le 2025/08/04 (UTC).
[[["Facile à comprendre","easyToUnderstand","thumb-up"],["J'ai pu résoudre mon problème","solvedMyProblem","thumb-up"],["Autre","otherUp","thumb-up"]],[["Il n'y a pas l'information dont j'ai besoin","missingTheInformationINeed","thumb-down"],["Trop compliqué/Trop d'étapes","tooComplicatedTooManySteps","thumb-down"],["Obsolète","outOfDate","thumb-down"],["Problème de traduction","translationIssue","thumb-down"],["Mauvais exemple/Erreur de code","samplesCodeIssue","thumb-down"],["Autre","otherDown","thumb-down"]],["Dernière mise à jour le 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,["# Advanced Sheets Service\n\nThe 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---------\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-----------\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\n### Read 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```javascript\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\n### Write 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```javascript\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\n### Add 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```javascript\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\n### Create 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```javascript\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```"]]
