您可以使用 Advanced Sheets 服務,透過 Apps Script 存取 Sheets API。這個 API 與 Apps Script 的內建 Google Sheets API 服務非常相似,可讓指令碼在 Google 試算表中讀取、編輯、設定格式及呈現資料。在大多數情況下,內建服務的使用方式較為簡單,但這項進階服務提供一些額外功能。
參考資料
如要進一步瞭解這項服務,請參閱 Sheets API 的參考說明文件。與 Apps Script 中的所有進階服務一樣,進階試算表服務會使用與公開 API 相同的物件、方法和參數。詳情請參閱「如何決定方法簽章」。
/** * 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('Failedtogetrangeofvaluesfromspreadsheet');}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failedwitherror%s',e.message);}}
/** * 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','ShipDate'],['$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('responsenull');}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failedwitherror%s',e.message);}}
/** * 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('CreatedsheetwithID:'+response.replies[0].addSheet.properties.sheetId);}catch(e){// TODO (developer) - Handle exceptionconsole.log('Failedwitherror%s',e.message);}}
/** * 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('Failedwitherror%s',e.message);}}
[[["容易理解","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"]],["上次更新時間:2024-12-21 (世界標準時間)。"],[[["The Advanced Sheets service allows Apps Script to interact with the Sheets API, enabling scripts to read, edit, format, and present data within Google Sheets."],["This advanced service offers additional features beyond the built-in Google Sheets service, but requires enabling before use."],["The service utilizes the same objects, methods, and parameters as the public Sheets API, mirroring its functionality within Apps Script."],["It provides access to version 4 of the Sheets API, enabling actions such as reading and writing data, adding sheets, and creating pivot tables."]]],[]]