Organiza tus páginas con colecciones Guarda y categoriza el contenido según tus preferencias.
El servicio de Hojas de cálculo avanzado te permite acceder a la API de Hojas de cálculo con Apps Script. Al igual que el servicio integrado de la API de Hojas de cálculo de Google de Apps Script, esta API permite que las secuencias de comandos lean, editen, formateen y presenten datos en Hojas de cálculo de Google. En la mayoría de los casos, el servicio integrado es más fácil de usar, pero este servicio avanzado proporciona algunas funciones adicionales.
Referencia
Para obtener información detallada sobre este servicio, consulta la documentación de referencia de la API de Sheets. Al igual que todos los servicios avanzados en Apps Script, el servicio avanzado de Hojas de cálculo usa los mismos objetos, métodos y parámetros que la API pública. Para obtener más información, consulta Cómo se determinan las firmas de métodos.
El siguiente código de ejemplo usa la versión 4 de la API, que es la única versión de la API de Sheets disponible actualmente como servicio avanzado en Apps Script.
Cómo leer valores de un rango
En el siguiente ejemplo, se muestra cómo leer valores de datos de un rango especificado en una hoja con el servicio avanzado de Sheets. Es equivalente a la muestra de la receta Cómo leer un solo rango.
/** * 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);}}
Escribe valores en varios rangos
En el siguiente ejemplo, se muestra cómo escribir datos en diferentes rangos disjuntos de una hoja con una sola solicitud. Es equivalente a la muestra de la receta Escribir en varios rangos.
/** * 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);}}
Cómo agregar una hoja nueva
En el siguiente ejemplo, se muestra cómo crear una hoja nueva con un tamaño y un color de pestaña específicos. Es equivalente a la muestra de la receta Agregar una hoja.
/** * 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);}}
Crear una tabla dinámica
En el siguiente ejemplo, se muestra cómo crear una tabla dinámica a partir de datos de origen. Es equivalente a la muestra de la receta Agregar una tabla dinámica.
/** * 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);}}
[[["Fácil de comprender","easyToUnderstand","thumb-up"],["Resolvió mi problema","solvedMyProblem","thumb-up"],["Otro","otherUp","thumb-up"]],[["Falta la información que necesito","missingTheInformationINeed","thumb-down"],["Muy complicado o demasiados pasos","tooComplicatedTooManySteps","thumb-down"],["Desactualizado","outOfDate","thumb-down"],["Problema de traducción","translationIssue","thumb-down"],["Problema con las muestras o los códigos","samplesCodeIssue","thumb-down"],["Otro","otherDown","thumb-down"]],["Última actualización: 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```"]]
