Google Apps Script 可讓您在 Google 試算表執行新奇有趣的操作。你可以使用 Apps Script 在 Google 試算表中新增自訂選單、對話方塊和側欄。您也可以為 Google 試算表編寫自訂函式，以及將 Google 試算表與其他 Google 服務 (例如 Google 日曆、雲端硬碟和 Gmail) 整合。
為 Google 試算表設計的指令碼大多會操控陣列，以便與試算表中的儲存格、列和欄互動。如果您不熟悉 JavaScript 中的陣列，Codecademy 提供陣列的絕佳訓練課程。(請注意，本課程並非由 Google 開發，也與 Google 無關)。
Apps Script 包含特殊 API，可讓您透過程式輔助方式建立、讀取及編輯 Google 試算表。Apps Script 可透過兩種方式與 Google 試算表互動：如果指令碼的使用者擁有試算表的適當權限，任何指令碼都能建立或修改試算表；指令碼也可以繫結至試算表，賦予指令碼變更使用者介面或在試算表開啟時做出回應的特殊能力。如要建立繫結指令碼，請在 Google 試算表中依序選取「擴充功能」>「Apps Script」。
試算表服務會將 Google 試算表視為格線，並以二維陣列運作。如要從試算表擷取資料，您必須取得儲存資料的試算表存取權、取得試算表中包含資料的範圍，然後取得儲存格的值。Apps Script 會讀取試算表中的結構化資料，並為這些資料建立 JavaScript 物件，方便您存取資料。
如果您打算將自訂介面發布為外掛程式，請遵循樣式指南，確保介面與 Google 試算表編輯器的樣式和版面配置一致。
連結至 Google 表單
您可以使用 Apps Script，透過 Forms 和 Spreadsheet 服務將 Google 表單連結至 Google 試算表。這項功能可根據試算表中的資料自動建立 Google 表單。您也可以使用 Apps Script 觸發條件 (例如 onFormSubmit)，在使用者填寫表單後執行特定動作。如要進一步瞭解如何將 Google 試算表連結至 Google 表單，請試試管理 Google 表單的回覆 5 分鐘快速入門課程。
您可以使用 Apps Script 存取 Google 試算表中的現有資料驗證規則，或建立新規則。舉例來說，下列範例說明如何設定資料驗證規則，只允許儲存格中的數字介於 1 到 100 之間。
functionvalidateMySpreadsheet(){//SetaruleforthecellB4tobeanumberbetween1and100.varcell=SpreadsheetApp.getActive().getRange('B4');varrule=SpreadsheetApp.newDataValidation().requireNumberBetween(1,100).setAllowInvalid(false).setHelpText('Number must be between 1 and 100.').build();cell.setDataValidation(rule);}
[[["容易理解","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 (世界標準時間)。"],[[["\u003cp\u003eGoogle Apps Script allows you to extend Google Sheets functionality by creating custom menus, dialogs, sidebars, and functions, as well as integrating with other Google services.\u003c/p\u003e\n"],["\u003cp\u003eApps Script interacts with Google Sheets data primarily through arrays, enabling you to read, write, and manipulate spreadsheet content programmatically.\u003c/p\u003e\n"],["\u003cp\u003eYou can customize the user interface of Google Sheets with Apps Script by adding custom menus, dialogs, and sidebars, and even connect it to Google Forms for automated workflows.\u003c/p\u003e\n"],["\u003cp\u003eApps Script provides functionalities to format cells, validate data, create charts, and define custom functions within your spreadsheets.\u003c/p\u003e\n"],["\u003cp\u003eGoogle Apps Script projects can be packaged as add-ons and published for others to use, and triggers can be set to automatically execute scripts based on specific events.\u003c/p\u003e\n"]]],[],null,["Google Apps Script lets you do new and cool things with Google Sheets. You can\nuse Apps Script to add [custom menus](/apps-script/guides/menus),\n[dialogs, and sidebars](/apps-script/guides/dialogs) to Google Sheets. It also\nlets you write [custom functions](/apps-script/guides/sheets/functions) for\nSheets, as well as integrate Sheets with other\n[Google services](/apps-script/guides/services) like Calendar, Drive, and\nGmail.\n\nMost scripts designed for Google Sheets manipulate arrays to\ninteract with the cells, rows, and columns in a spreadsheet. If you're not\nfamiliar with arrays in JavaScript, Codecademy offers a\n[great training module for arrays](https://www.codecademy.com/learn/introduction-to-javascript/modules/learn-javascript-arrays).\n(Note that this course wasn't developed by and isn't associated with Google.)\n\nFor a quick introduction to using Apps Script with Google Sheets, see the\n5-minute quickstart guide for\n[Macros, Menus, and Custom Functions](/apps-script/quickstart/macros).\n\nGet started\n\nApps Script includes special APIs to let you programmatically create, read, and\nedit Google Sheets. Apps Script can interact with Google Sheets in two broad\nways: any script can create or modify a spreadsheet if the script's user has\nappropriate permissions for the spreadsheet, and a script can also be\n[bound](/apps-script/guides/bound) to a spreadsheet, which\ngives the script special abilities to alter the user interface or respond when\nthe spreadsheet is opened. To create a bound script, select\n**Extensions** \\\u003e **Apps Script** from within\nGoogle Sheets.\n\nThe [Spreadsheet service](/apps-script/reference/spreadsheet) treats Google\nSheets as a grid, operating with two-dimensional arrays. To retrieve the data\nfrom the spreadsheet, you must get access to the spreadsheet where the data is\nstored, get the range in the spreadsheet that holds the data, and then get the\nvalues of the cells. Apps Script facilitates access to the data by reading\nstructured data in the spreadsheet and creating JavaScript objects for them.\n\nReading data\n\nSuppose you have a list of product names and product numbers that you store in\na spreadsheet, as shown in the image below.\n\nThe example below shows how to retrieve and log the product names and product\nnumbers. \n\n function logProductInfo() {\n var sheet = SpreadsheetApp.getActiveSheet();\n var data = sheet.getDataRange().getValues();\n for (var i = 0; i \u003c data.length; i++) {\n Logger.log('Product name: ' + data[i][0]);\n Logger.log('Product number: ' + data[i][1]);\n }\n }\n\nView logs\n\nTo view the data that has been logged, at the top of the script editor, click\n**Execution log**.\n\nWriting data\n\nTo store data, such as a new product name and number to the\nspreadsheet, add the following code to the end of the script. \n\n function addProduct() {\n var sheet = SpreadsheetApp.getActiveSheet();\n sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);\n }\n\nThe above code appends a new row at the bottom of the spreadsheet, with the\nvalues specified. If you run this function, you'll see a new row added to the\nspreadsheet.\n\nCustom menus and user interfaces\n\nYou can customize Google Sheets by adding custom menus, dialog boxes, and\nsidebars. To learn the basics of creating menus, see the\n[guide to menus](/apps-script/guides/menus). To learn about customizing the\ncontent of a dialog box, see the\n[guide to HTML service](/apps-script/guides/html#serve_html_as_a_google_docs_sheets_or_forms_user_interface).\n\nYou can also attach a script function to an image or drawing within a\nspreadsheet; the function will execute when a user clicks on the image or\ndrawing. To learn more, see [Images and Drawings in Google Sheets](/apps-script/guides/menus#clickable_images_and_drawings_in_google_sheets).\n\nIf you're planning to publish your custom interface as part of an\n[add-on](#add-ons_for_google_sheets), follow the\n[style guide](/workspace/add-ons/guides/editor-style) for consistency with the\nstyle and layout of the Google Sheets editor.\n\nConnecting to Google Forms\n\nApps Script allows you to connect Google Forms with Google Sheets through\n[Forms](/apps-script/reference/forms) and\n[Spreadsheet](/apps-script/reference/spreadsheet) services. This feature\ncan automatically create a Google Form based on data in a spreadsheet.\nApps Script also enables you to use [triggers](#triggers), such as\n`onFormSubmit` to perform a specific action after a user responds to the form.\nTo learn more about connecting Google Sheets to Google Forms, try the [Managing\nResponses for Google Forms](/apps-script/quickstart/forms) 5-minute quickstart.\n\nFormatting\n\nThe [`Range`](/apps-script/reference/spreadsheet/range) class has methods like\n[`setBackground(color)`](/apps-script/reference/spreadsheet/range#setBackground(String))\nto access and modify the format of a cell or range of cells. The following\nexample shows how you can set the font style of a range: \n\n function formatMySpreadsheet() {\n // Set the font style of the cells in the range of B2:C2 to be italic.\n var ss = SpreadsheetApp.getActiveSpreadsheet();\n var sheet = ss.getSheets()[0];\n var cell = sheet.getRange('B2:C2');\n cell.setFontStyle('italic');\n }\n\nData validation\n\nApps Script lets you access existing data-validation rules in Google Sheets\nor create new rules. For instance, the following sample shows how to set a\ndata-validation rule that allows only numbers between 1 and 100 on a cell. \n\n function validateMySpreadsheet() {\n // Set a rule for the cell B4 to be a number between 1 and 100.\n var cell = SpreadsheetApp.getActive().getRange('B4');\n var rule = SpreadsheetApp.newDataValidation()\n .requireNumberBetween(1, 100)\n .setAllowInvalid(false)\n .setHelpText('Number must be between 1 and 100.')\n .build();\n cell.setDataValidation(rule);\n }\n\nFor more details on working with data-validation rules, see\n[`SpreadsheetApp.newDataValidation()`](/apps-script/reference/spreadsheet/spreadsheet-app#newDataValidation()),\n[`DataValidationBuilder`](/apps-script/reference/spreadsheet/data-validation-builder),\nand [`Range.setDataValidation(rule)`](/apps-script/reference/spreadsheet/range#setDataValidation(DataValidation))\n\nCharts\n\nApps Script lets you embed charts in a spreadsheet that represent the data in a\nspecific range. The following example generates an embedded bar chart, assuming\nyou have chartable data in cells `A1:B15`: \n\n function newChart() {\n // Generate a chart representing the data in the range of A1:B15.\n var ss = SpreadsheetApp.getActiveSpreadsheet();\n var sheet = ss.getSheets()[0];\n\n var chart = sheet.newChart()\n .setChartType(Charts.ChartType.BAR)\n .addRange(sheet.getRange('A1:B15'))\n .setPosition(5, 5, 0, 0)\n .build();\n\n sheet.insertChart(chart);\n }\n\nTo learn more about embedding a chart into your spreadsheet,\nsee [`EmbeddedChart`](/apps-script/reference/spreadsheet/embedded-chart) and\nspecific chart builders, such as\n[`EmbeddedPieChartBuilder`](/apps-script/reference/spreadsheet/embedded-pie-chart-builder).\n\nCustom functions in Google Sheets\n\nA [custom function](/apps-script/guides/sheets/functions) is similar to a\nbuilt-in spreadsheet function like `=SUM(A1:A5)` except that you define the\nfunctions's behavior with Apps Script. For example, you could create a custom\nfunction, `in2mm()`, that converts a value from inches to millimeters, then use\nthe formula in your spreadsheet by typing `=in2mm(A1)` or `=in2mm(10)` into a\ncell.\n\nTo learn more about custom functions, try the\n[Menus and Custom Functions](/apps-script/quickstart/custom-functions)\n5-minute quickstart, or take a look at the more in-depth\n[guide to custom functions](/apps-script/guides/sheets/functions).\n\nMacros\n\nMacros are another way of executing Apps Script code from the Google Sheets UI.\nUnlike custom functions, you activate them with a keyboard shortcut or through\nthe Google Sheets menu. For more information, see\n[Google Sheets Macros](/apps-script/guides/sheets/macros).\n\nAdd-ons for Google Sheets\n\n[Add-ons](/workspace/add-ons/overview) are specially packaged Apps Script\nprojects that run inside Google Sheets and can be installed\nfrom the Google Sheets add-on store. If you've developed a script for Google Sheets\nand want to share it with the world, Apps Script lets you\n[publish](/workspace/add-ons/how-tos/editor-publish-overview) your script as an\nadd-on so other users can install it from the add-on store.\n\nTriggers\n\nScripts that are [bound](/apps-script/guides/bound) to a Google Sheets file\ncan use [simple triggers](/apps-script/guides/triggers) like the functions\n`onOpen()` and `onEdit()` to respond automatically when a user who has edit\naccess to the spreadsheet opens or edits the spreadsheet.\n\nLike simple triggers,\n[installable triggers](/apps-script/guides/triggers/installable) let Google Sheets\nrun a function automatically when a certain event occurs. Installable\ntriggers, however, offer more flexibility than simple triggers and support\nthe following events: open, edit, change, form submit, and time-driven (clock)."]]
