進階試算表服務

您可以使用 Advanced Sheets 服務，透過 Apps Script 存取 Sheets API。與 Apps Script 的內建 Google Sheets API 服務類似， 這個 API 可讓指令碼讀取、編輯、格式化及呈現 Google 試算表中的資料。 在大多數情況下，內建服務較容易使用，但這項進階服務提供了一些額外功能。

參考資料

如要進一步瞭解這項服務，請參閱 Google Sheets API 的參考文件。與 Apps Script 中的所有進階服務一樣，進階 Google 試算表服務使用的物件、方法和參數，都與公開 API 相同。詳情請參閱「如何判斷方法簽章」。

如要回報問題及尋求其他支援，請參閱 Google 試算表支援指南。

程式碼範例

下列程式碼範例使用 API 的第 4 版；目前只有這個版本的 Google 試算表 API 可做為 Apps Script 的進階服務。

從範圍讀取值

以下範例說明如何使用 Google 試算表進階服務，從試算表中的指定範圍讀取資料值。這相當於「讀取單一範圍」食譜範例。

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);
  }
}

將值寫入多個範圍

以下範例示範如何透過單一要求，將資料寫入試算表中不同的不相交範圍。這等同於「寫入多個範圍」食譜範例。

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);
  }
}

新增工作表

以下範例說明如何建立具有特定大小和分頁標籤顏色的新工作表。這相當於「新增工作表」方案範例。

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);
  }
}

建立資料透視表

以下範例示範如何從來源資料建立樞紐分析表。 這相當於「新增資料透視表」方案範例。

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);
  }
}