Class SpreadsheetApp

// The code below creates a new spreadsheet "Finances" and logs the URL for it const ssNew = 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 const ssNew = 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. const ss = 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. const ss = 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. const ss = 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. function colors() {   const sheet = SpreadsheetApp.getActiveSheet();   for (let i = 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. const colorObject = SpreadsheetApp.getActiveRange().getBackgroundObject(); // Assume the color has ColorType.RGB. Logger.log(colorObject.asRgbColor().asHexString());

// Returns the list of active ranges. const rangeList = 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. const currentCell = SpreadsheetApp.getCurrentCell();

const selection = SpreadsheetApp.getSelection(); const currentCell = selection.getCurrentCell();

// Add a custom menu to the active spreadsheet, including a separator and a // sub-menu. function onOpen(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. const ss = SpreadsheetApp.openByUrl(     'https://docs.google.com/spreadsheets/d/abc123456/edit', );  // Gets Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets cell A1 on Sheet1. const range = sheet.getRange('A1');  // Builds an image using a source URL. const cellImage =     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);

const rgbColor = 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. const sheet = SpreadsheetApp.getActive().getActiveSheet(); const range = sheet.getRange('A1:B3'); const rule = SpreadsheetApp.newConditionalFormatRule()                  .whenNumberBetween(1, 10)                  .setBackground('#FF0000')                  .setRanges([range])                  .build(); const rules = 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. const ss = 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. const dataSourceSpec = 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);

const cell = SpreadsheetApp.getActive().getRange('A1'); const rule = 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. const ss = SpreadsheetApp.openByUrl(     'https://docs.google.com/spreadsheets/d/abc123456/edit', );  // Gets Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Sets the range to A1:D20. const range = 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. const filter = range.getFilter(); const criteria = 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. const cell = SpreadsheetApp.getActive().getRange('A1'); const bold = SpreadsheetApp.newTextStyle().setBold(true).build(); const value = 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. const range = SpreadsheetApp.getActive().getRange('A1:B3'); const style = 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. const files = DriveApp.searchFiles(     `starred = true and mimeType = "${MimeType.GOOGLE_SHEETS}"`, ); while (files.hasNext()) {   const spreadsheet = SpreadsheetApp.open(files.next());   const sheet = 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). const ss = 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). const ss = 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. const range =     SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('C1:D4'); SpreadsheetApp.setActiveRange(range);  const selection = SpreadsheetApp.getSelection(); // Current cell: C1 const currentCell = selection.getCurrentCell(); // Active Range: C1:D4 const activeRange = selection.getActiveRange();

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

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

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const firstSheet = spreadsheet.getSheets()[0]; const secondSheet = 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 const range = spreadsheet.getActiveSheet().getSelection().getActiveRange(); Logger.log(range.getA1Notation());

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

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