This is my first attempt with script editor. I was assigned to do a script to crate pivot table for google sheet.
//creating pivot table through script editor for google sheet
function addPivotTable() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = "Sheet1";
// Create a new sheet which will contain our Pivot Table
var pivotTableSheet = ss.insertSheet();
var pivotTableSheetId = pivotTableSheet.getSheetId();
// Add Pivot Table to new sheet
// Meaning we send an 'updateCells' request to the Sheets API
// Specifying via 'start' the sheet where we want to place our Pivot Table
// And in 'rows' the parameters of our Pivot Table
var requests = [{
// Meaning we send an 'updateCells' request to the Sheets API
"updateCells": {
// And in 'rows' the parameters of our Pivot Table
"rows": {
"values": [
{
// Add Pivot Table to new sheet
"pivotTable": {
"source": {
"sheetId": ss.getSheetByName(sheetName).getSheetId(),
"startRowIndex": 0,
"startColumnIndex": 0,
},
//create rows from the selected columns
"rows": [
{
"sourceColumnOffset": 14,
"showTotals": true,
"sortOrder": "ASCENDING",
},
],
//show values from the selected columns
"values": [
{
"summarizeFunction": "COUNTA",
"sourceColumnOffset": 10
}
],
//display in horizontal layout
"valueLayout": "HORIZONTAL"
}
}
]
},
// Specifying via 'start' the sheet where we want to place our Pivot Table
"start": {
"sheetId": pivotTableSheetId,
},
"fields": "pivotTable"
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': [requests]}, ss.getId());
}
Please do check my code and explain where did i went wrong as every time im running the script editor error telling sheet is not defined popup. "ReferenceError: "Sheets" is not defined. (line 46, file "Code")Dismiss"