進階試算表服務可讓您使用 Sheets API
Apps Script。這個 API 與 Apps Script 的內建 Google Sheets API 服務非常相似,可讓指令碼在 Google 試算表中讀取、編輯、設定格式及呈現資料。在大多數情況下,內建服務的使用方式較為簡單,但這項進階服務提供一些額外功能。
參考資料
如要進一步瞭解這項服務,請參閱 Sheets API 的參考說明文件。如同 Apps Script 的所有進階服務,進階試算表服務一樣採用
物件、方法和參數詳情請參閱如何判定方法簽章。
/** * 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);}}