Servicio avanzado de Hojas de cálculo

desde Apps Script para leer, editar, dar formato y presentar datos en Hojas de cálculo de Google.

El servicio avanzado de Hojas de cálculo te permite acceder a la API de Sheets con Google Apps Script. Al igual que el servicio integrado de la API de Google Sheets de Apps Script, esta API permite que las secuencias de comandos lean, editen, den formato 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.

Este es un servicio avanzado que se debe habilitar antes de usarlo.

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 los métodos.

Para informar problemas y encontrar otra asistencia, consulta la guía de asistencia de Hojas de cálculo.

Código de muestra

En el siguiente código de muestra, se usa la versión 4 de la API. Esta es la única versión de la API de Sheets disponible como un servicio avanzado en Apps Script.

Lee 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 Hojas de cálculo. Es equivalente a la muestra de receta Leer un solo rango.

advanced/sheets.gs
/**
 * 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
 */
function readRange(spreadsheetId = yourspreadsheetId) {
  try {
    const response = 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 exception
    console.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 en una hoja con una solicitud. Es equivalente a la muestra de receta Escribir en varios rangos.

advanced/sheets.gs
/**
 * 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
 */
function writeToMultipleRanges(spreadsheetId = yourspreadsheetId) {
  // Specify some values to write to the sheet.
  const columnAValues = [["Item", "Wheel", "Door", "Engine"]];
  const rowValues = [
    ["Cost", "Stocked", "Ship Date"],
    ["$20.50", "4", "3/1/2016"],
  ];

  const request = {
    valueInputOption: "USER_ENTERED",
    data: [
      {
        range: "Sheet1!A1:A4",
        majorDimension: "COLUMNS",
        values: columnAValues,
      },
      {
        range: "Sheet1!B1:D2",
        majorDimension: "ROWS",
        values: rowValues,
      },
    ],
  };
  try {
    const response = Sheets.Spreadsheets.Values.batchUpdate(
      request,
      spreadsheetId,
    );
    if (response) {
      console.log(response);
      return;
    }
    console.log("response null");
  } catch (e) {
    // TODO (developer) - Handle  exception
    console.log("Failed with error %s", e.message);
  }
}

Agrega una hoja nueva

En el siguiente ejemplo, se muestra cómo crear una hoja nueva con un tamaño específico y un color de pestaña. Es equivalente a la muestra de receta Agregar una hoja.

advanced/sheets.gs
/**
 * 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
 */
function addSheet(spreadsheetId = yourspreadsheetId) {
  const requests = [
    {
      addSheet: {
        properties: {
          title: "Deposits",
          gridProperties: {
            rowCount: 20,
            columnCount: 12,
          },
          tabColor: {
            red: 1.0,
            green: 0.3,
            blue: 0.4,
          },
        },
      },
    },
  ];
  try {
    const response = Sheets.Spreadsheets.batchUpdate(
      { requests: requests },
      spreadsheetId,
    );
    console.log(
      `Created sheet with ID: ${response.replies[0].addSheet.properties.sheetId}`,
    );
  } catch (e) {
    // TODO (developer) - Handle exception
    console.log("Failed with error %s", e.message);
  }
}

Crea 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 receta Agregar una tabla dinámica.

advanced/sheets.gs
/**
 * 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
 */
function addPivotTable(
  spreadsheetId = yourspreadsheetId,
  pivotSourceDataSheetId = yourpivotSourceDataSheetId,
  destinationSheetId = yourdestinationSheetId,
) {
  const requests = [
    {
      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 {
    const response = Sheets.Spreadsheets.batchUpdate(
      { requests: requests },
      spreadsheetId,
    );
    // The Pivot table will appear anchored to cell A50 of the destination sheet.
  } catch (e) {
    // TODO (developer) - Handle exception
    console.log("Failed with error %s", e.message);
  }
}