Class SpreadsheetApp

// The code below creates a new spreadsheet "Finances" and logs the URL for it
constssNew=SpreadsheetApp.create('Finances');
Logger.log(ssNew.getUrl());

// The code below creates a new spreadsheet "Finances" with 50 rows and 5
// columns and logs the URL for it
constssNew=SpreadsheetApp.create('Finances',50,5);
Logger.log(ssNew.getUrl());

// Turns data execution on for all types of data sources.
SpreadsheetApp.enableAllDataSourcesExecution();
// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
constss=SpreadsheetApp.openById('abc123456');
// Gets the first data source sheet in the spreadsheet and refreshes the data.
ss.getDataSourceSheets()[0].refreshData();

// Turns data execution on for BigQuery data sources.
SpreadsheetApp.enableBigQueryExecution();
// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
constss=SpreadsheetApp.openById('abc123456');
// Gets the first data source sheet in the spreadsheet and refreshes the
// BigQuery data.
ss.getDataSourceSheets()[0].refreshData();

// Turns data execution on for Looker data sources.
SpreadsheetApp.enableLookerExecution();
// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
constss=SpreadsheetApp.openById('abc123456');
// Gets the first data source sheet in the spreadsheet and refreshes the
// associated Looker data.
ss.getDataSourceSheets()[0].refreshData();

// The code below changes the background color of cells A1 and B1 twenty times.
// You should be able to see the updates live in the spreadsheet. If flush() is
// not called, the updates may be applied live or may all be applied at once
// when the script completes.
functioncolors(){
constsheet=SpreadsheetApp.getActiveSheet();
for(leti=0;i < 20;i++){
if(i%2===0){
sheet.getRange('A1').setBackground('green');
sheet.getRange('B1').setBackground('red');
}else{
sheet.getRange('A1').setBackground('red');
sheet.getRange('B1').setBackground('green');
}
SpreadsheetApp.flush();
}
}

// The code below logs the URL for the active spreadsheet.
Logger.log(SpreadsheetApp.getActive().getUrl());

// The code below logs the background color for the active range.
constcolorObject=SpreadsheetApp.getActiveRange().getBackgroundObject();
// Assume the color has ColorType.RGB.
Logger.log(colorObject.asRgbColor().asHexString());

// Returns the list of active ranges.
constrangeList=SpreadsheetApp.getActiveRangeList();

// The code below logs the name of the active sheet.
Logger.log(SpreadsheetApp.getActiveSheet().getName());

// The code below logs the URL for the active spreadsheet.
Logger.log(SpreadsheetApp.getActiveSpreadsheet().getUrl());

// Returns the current highlighted cell in the one of the active ranges.
constcurrentCell=SpreadsheetApp.getCurrentCell();

constselection=SpreadsheetApp.getSelection();
constcurrentCell=selection.getCurrentCell();

// Add a custom menu to the active spreadsheet, including a separator and a
// sub-menu.
functiononOpen(e){
SpreadsheetApp.getUi()
.createMenu('My Menu')
.addItem('My menu item','myFunction')
.addSeparator()
.addSubMenu(
SpreadsheetApp.getUi()
.createMenu('My sub-menu')
.addItem('One sub-menu item','mySecondFunction')
.addItem('Another sub-menu item','myThirdFunction'),
)
.addToUi();
}

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets cell A1 on Sheet1.
constrange=sheet.getRange('A1');
// Builds an image using a source URL.
constcellImage=
SpreadsheetApp.newCellImage()
.setSourceUrl(
'https://www.gstatic.com/images/branding/productlogos/apps_script/v10/web-64dp/logo_apps_script_color_1x_web_64dp.png',
)
.build();
// Sets the image in cell A1.
range.setValue(cellImage);

constrgbColor=SpreadsheetApp.newColor().setRgbColor('#FF0000').build();

// Adds a conditional format rule to a sheet that causes all cells in range
// A1:B3 to turn red if they contain a number between 1 and 10.
constsheet=SpreadsheetApp.getActive().getActiveSheet();
constrange=sheet.getRange('A1:B3');
construle=SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween(1,10)
.setBackground('#FF0000')
.setRanges([range])
.build();
construles=sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Enables BigQuery.
SpreadsheetApp.enableBigQueryExecution();
// Builds a data source specification.
// TODO (developer): Update the project ID to your own Google Cloud project ID.
constdataSourceSpec=SpreadsheetApp.newDataSourceSpec()
.asBigQuery()
.setProjectId('project-id-1')
.setTableProjectId('bigquery-public-data')
.setDatasetId('ncaa_basketball')
.setTableId('mbb_historical_teams_games')
.build();
// Adds the data source and its data to the spreadsheet.
ss.insertDataSourceSheet(dataSourceSpec);

constcell=SpreadsheetApp.getActive().getRange('A1');
construle=SpreadsheetApp.newDataValidation()
.requireNumberBetween(1,100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Sets the range to A1:D20.
constrange=sheet.getRange('A1:D20');
// Creates a filter and applies it to the specified range.
range.createFilter();
// Gets the current filter for the range and creates filter criteria that only
// shows cells that aren't empty.
constfilter=range.getFilter();
constcriteria=SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
// Sets the criteria to column C.
filter.setColumnFilterCriteria(3,criteria);

// Sets cell A1 to have the text "Hello world", with "Hello" bolded.
constcell=SpreadsheetApp.getActive().getRange('A1');
constbold=SpreadsheetApp.newTextStyle().setBold(true).build();
constvalue=SpreadsheetApp.newRichTextValue()
.setText('Hello world')
.setTextStyle(0,5,bold)
.build();
cell.setRichTextValue(value);

// Sets range A1:B3 to have red, size 22, bolded, underlined text.
constrange=SpreadsheetApp.getActive().getRange('A1:B3');
conststyle=SpreadsheetApp.newTextStyle()
.setForegroundColor('red')
.setFontSize(22)
.setBold(true)
.setUnderline(true)
.build();
range.setTextStyle(style);

// Get any starred spreadsheets from Google Drive, then open the spreadsheets
// and log the name of the first sheet within each spreadsheet.
constfiles=DriveApp.searchFiles(
`starred = true and mimeType = "${MimeType.GOOGLE_SHEETS}"`,
);
while(files.hasNext()){
constspreadsheet=SpreadsheetApp.open(files.next());
constsheet=spreadsheet.getSheets()[0];
Logger.log(sheet.getName());
}

// The code below opens a spreadsheet using its ID and logs the name for it.
// Note that the spreadsheet is NOT physically opened on the client side.
// It is opened on the server only (for modification by the script).
constss=SpreadsheetApp.openById('abc1234567');
Logger.log(ss.getName());

// Opens a spreadsheet by its URL and logs its name.
// Note that the spreadsheet doesn't physically open on the client side.
// It opens on the server only (for modification by the script).
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc1234567/edit',
);
console.log(ss.getName());

// The code below sets range C1:D4 in the first sheet as the active range.
constrange=
SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1:D4');
SpreadsheetApp.setActiveRange(range);
constselection=SpreadsheetApp.getSelection();
// Current cell: C1
constcurrentCell=selection.getCurrentCell();
// Active Range: C1:D4
constactiveRange=selection.getActiveRange();

// The code below sets ranges [D4, B2:C4] in the active sheet as the active
// ranges.
constrangeList=SpreadsheetApp.getActiveSheet().getRanges(['D4','B2:C4']);
SpreadsheetApp.setActiveRangeList(rangeList);
constselection=SpreadsheetApp.getSelection();
// Current cell: B2
constcurrentCell=selection.getCurrentCell();
// Active range: B2:C4
constactiveRange=selection.getActiveRange();
// Active range list: [D4, B2:C4]
constactiveRangeList=selection.getActiveRangeList();

// The code below makes the 2nd sheet active in the active spreadsheet.
constspreadsheet=SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[1]);

constspreadsheet=SpreadsheetApp.getActiveSpreadsheet();
constfirstSheet=spreadsheet.getSheets()[0];
constsecondSheet=spreadsheet.getSheets()[1];
// Set the first sheet as the active sheet and select the range D4:F4.
spreadsheet.setActiveSheet(firstSheet).getRange('D4:F4').activate();
// Switch to the second sheet to do some work.
spreadsheet.setActiveSheet(secondSheet);
// Switch back to first sheet, and restore its selection.
spreadsheet.setActiveSheet(firstSheet,true);
// The selection of first sheet is restored, and it logs D4:F4
constrange=spreadsheet.getActiveSheet().getSelection().getActiveRange();
Logger.log(range.getA1Notation());

// The code below makes the spreadsheet with key "1234567890" the active
// spreadsheet
constss=SpreadsheetApp.openById('1234567890');
SpreadsheetApp.setActiveSpreadsheet(ss);

// The code below sets the cell B5 in the first sheet as the current cell.
constcell=
SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('B5');
SpreadsheetApp.setCurrentCell(cell);
constselection=SpreadsheetApp.getSelection();
// Current cell: B5
constcurrentCell=selection.getCurrentCell();