// The code below creates a new spreadsheet "Finances" and logs the URL for itconstssNew=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 itconstssNew=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: C1constcurrentCell=selection.getCurrentCell();// Active Range: C1:D4constactiveRange=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: B2constcurrentCell=selection.getCurrentCell();// Active range: B2:C4constactiveRange=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:F4constrange=spreadsheet.getActiveSheet().getSelection().getActiveRange();Logger.log(range.getA1Notation());

// The code below makes the spreadsheet with key "1234567890" the active// spreadsheetconstss=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: B5constcurrentCell=selection.getCurrentCell();