Class Range

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; const range = sheet.getRange('A1:D10'); range.activate();  const selection = sheet.getSelection(); // Current cell: A1 const currentCell = selection.getCurrentCell(); // Active Range: A1:D10 const activeRange = selection.getActiveRange();

// Gets the first sheet of the spreadsheet. const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];  // Gets the cell B5 and sets it as the active cell. const range = sheet.getRange('B5'); const currentCell = range.activateAsCurrentCell();  // Logs the activated cell. console.log(currentCell.getA1Notation());

// 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 row 2 on the sheet. const range = sheet.getRange('2:2');  // Adds the key 'NAME' to the developer metadata for row 2. range.addDeveloperMetadata('NAME');  // Gets the metadata and logs it to the console. const developerMetaData = range.getDeveloperMetadata()[0]; console.log(developerMetaData.getKey());

// 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 row 2 on Sheet1. const range = sheet.getRange('2:2');  // Adds the key 'NAME' and sets the developer metadata visibility to 'DOCUMENT' // for row 2 on Sheet1. range.addDeveloperMetadata(     'NAME',     SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT, );  // Gets the updated metadata info and logs it to the console. const developerMetaData = range.getDeveloperMetadata()[0]; console.log(developerMetaData.getKey()); console.log(developerMetaData.getVisibility().toString());

// 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 row 2 of Sheet1. const range = sheet.getRange('2:2');  // Adds the key 'NAME' and sets the value to 'GOOGLE' for the metadata of row 2. range.addDeveloperMetadata('NAME', 'GOOGLE');  // Gets the metadata and logs it to the console. const developerMetaData = range.getDeveloperMetadata()[0]; console.log(developerMetaData.getKey()); console.log(developerMetaData.getValue());

// 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 row 2 on the sheet. const range = sheet.getRange('2:2');  // Adds the key 'NAME', sets the value to 'GOOGLE', and sets the visibility // to PROJECT for row 2 on the sheet. range.addDeveloperMetadata(     'NAME',     'GOOGLE',     SpreadsheetApp.DeveloperMetadataVisibility.PROJECT, );  // Gets the updated metadata info and logs it to the console. const developerMetaData = range.getDeveloperMetadata()[0]; console.log(developerMetaData.getKey()); console.log(developerMetaData.getValue()); console.log(developerMetaData.getVisibility().toString());

// 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 row 2 on the sheet. const range = sheet.getRange('2:2');  // Applies column banding to row 2. const colBanding = range.applyColumnBanding();  // Gets the first banding on the sheet and logs the color of the header column. console.log(     sheet.getBandings()[0]         .getHeaderColumnColorObject()         .asRgbColor()         .asHexString(), );  // Gets the first banding on the sheet and logs the color of the second column. console.log(     sheet.getBandings()[0]         .getSecondColumnColorObject()         .asRgbColor()         .asHexString(), );

// 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 row 2 on the sheet. const range = sheet.getRange('2:2');  // Applies the INDIGO color banding theme to the columns in row 2. const colBanding = range.applyColumnBanding(SpreadsheetApp.BandingTheme.INDIGO);  // Gets the first banding on the sheet and logs the color of the second column. console.log(     sheet.getBandings()[0]         .getSecondColumnColorObject()         .asRgbColor()         .asHexString(), );

// 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 rows 12-22 on the sheet. const range = sheet.getRange('12:22');  // Applies the BLUE color banding theme to rows 12-22. // Sets the header visibility to false and the footer visibility to true. const colBanding = range.applyColumnBanding(     SpreadsheetApp.BandingTheme.BLUE,     false,     true, );  // Gets the banding color and logs it to the console. console.log(     sheet.getBandings()[0]         .getSecondColumnColorObject()         .asRgbColor()         .asHexString(), );  // Gets the header color object and logs it to the console. Returns null because // the header visibility is set to false. console.log(sheet.getBandings()[0].getHeaderColumnColorObject());  // Gets the footer color and logs it to the console. console.log(     sheet.getBandings()[0]         .getFooterColumnColorObject()         .asRgbColor()         .asHexString(), );

// Opens the spreadsheet by its URL. If you created your script from within a // Google Sheets spreadsheet, 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 rows 1-30 on Sheet1. const range = sheet.getRange('1:30');  // Applies row banding to rows 1-30. range.applyRowBanding();  // Gets the hex color of the second banded row. const secondRowColor =     range.getBandings()[0].getSecondRowColorObject().asRgbColor().asHexString();  // Logs the hex color to console. console.log(secondRowColor);

// Opens the spreadsheet by its URL. If you created your script from within a // Google Sheets spreadsheet, 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 rows 1-30 on Sheet1. const range = sheet.getRange('1:30');  // Applies the INDIGO row banding theme to rows 1-30. range.applyRowBanding(SpreadsheetApp.BandingTheme.INDIGO);  // Gets the hex color of the second banded row. const secondRowColor =     range.getBandings()[0].getSecondRowColorObject().asRgbColor().asHexString();  // Logs the hex color to console. console.log(secondRowColor);

// Opens the spreadsheet by its URL. If you created your script from within a // Google Sheets spreadsheet, 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 rows 1-30 on Sheet1. const range = sheet.getRange('1:30');  // Applies the INDIGO row banding to rows 1-30 and // specifies to hide the header and show the footer. range.applyRowBanding(SpreadsheetApp.BandingTheme.INDIGO, false, true);

const sheet = SpreadsheetApp.getActiveSheet();  // Has values [1, 2, 3, 4]. const sourceRange = sheet.getRange('A1:A4'); // The range to fill with values. const destination = sheet.getRange('A1:A20');  // Inserts new values in A5:A20, continuing the pattern expressed in A1:A4 sourceRange.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

const sheet = SpreadsheetApp.getActiveSheet();  // A1:A20 has values [1, 2, 3, ... 20]. // B1:B4 has values [1/1/2017, 1/2/2017, ...] const sourceRange = sheet.getRange('B1:B4');  // Results in B5:B20 having values [1/5/2017, ... 1/20/2017] sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

// Opens the spreadsheet by its URL. If you created your script from within a // Google Sheets spreadsheet, 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 the range A1:C6 on Sheet1. const range = sheet.getRange('A1:C6');  // Unmerges the range A1:C6 into individual cells. range.breakApart();

// Opens the spreadsheet by its URL. If you created your script from within a // Google Sheets spreadsheet, 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 the range A1:C6 on Sheet1. const range = sheet.getRange('A1:C6');  // Logs whether the user has permission to edit every cell in the range. console.log(range.canEdit());

// Changes the state of cells which currently contain either the checked or // unchecked value configured in the range A1:B10 to 'checked'. const range = SpreadsheetApp.getActive().getRange('A1:B10'); range.check();

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('A1:D10'); range.clear();

// The code below clears range C2:G7 in the active sheet, but preserves the // format, data validation rules, and comments. SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 5).clear({   contentsOnly: true });

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('A1:D10'); range.clearContent();

// Clear the data validation rules for cells A1:B5. const range = SpreadsheetApp.getActive().getRange('A1:B5'); range.clearDataValidations();

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('A1:D10'); range.clearFormat();

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('A1:D10'); range.clearNote();

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; const range = sheet.getActiveRange();  // All row and column groups within the range are collapsed. range.collapseGroups();

const ss = SpreadsheetApp.getActiveSpreadsheet(); const source = ss.getSheets()[0];  const range = source.getRange('B2:D4');  // This copies the formatting in B2:D4 in the source sheet to // D4:F6 in the sheet with gridId 1555299895. Note that you can get the gridId // of a sheet by calling sheet.getSheetId() or range.getGridId(). range.copyFormatToRange(1555299895, 4, 6, 4, 6);

const ss = SpreadsheetApp.getActiveSpreadsheet(); const source = ss.getSheets()[0]; const destination = ss.getSheets()[1];  const range = source.getRange('B2:D4');  // This copies the formatting in B2:D4 in the source sheet to // D4:F6 in the second sheet range.copyFormatToRange(destination, 4, 6, 4, 6);

// The code below copies the first 5 columns over to the 6th column. const sheet = SpreadsheetApp.getActiveSheet(); const rangeToCopy = sheet.getRange(1, 1, sheet.getMaxRows(), 5); rangeToCopy.copyTo(sheet.getRange(1, 6));

// The code below copies only the values of the first 5 columns over to the 6th // column. const sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange('A:E').copyTo(     sheet.getRange('F1'),     SpreadsheetApp.CopyPasteType.PASTE_VALUES,     false, );

// The code below copies only the values of the first 5 columns over to the 6th // column. const sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange('A:E').copyTo(sheet.getRange('F1'), {contentsOnly: true});

const ss = SpreadsheetApp.getActiveSpreadsheet(); const source = ss.getSheets()[0];  const range = source.getRange('B2:D4');  // This copies the data in B2:D4 in the source sheet to // D4:F6 in the sheet with gridId 0 range.copyValuesToRange(0, 4, 6, 4, 6);

const ss = SpreadsheetApp.getActiveSpreadsheet(); const source = ss.getSheets()[0]; const destination = ss.getSheets()[1];  const range = source.getRange('B2:D4');  // This copies the data in B2:D4 in the source sheet to // D4:F6 in the second sheet range.copyValuesToRange(destination, 4, 6, 4, 6);

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const anchorCell = spreadsheet.getSheets()[0].getRange('A1'); const dataSource = spreadsheet.getDataSources()[0];  const pivotTable = anchorCell.createDataSourcePivotTable(dataSource); pivotTable.addRowGroup('dataColumnA'); pivotTable.addColumnGroup('dataColumnB'); pivotTable.addPivotValue(     'dataColumnC',     SpreadsheetApp.PivotTableSummarizeFunction.SUM, ); pivotTable.addFilter(     'dataColumnA',     SpreadsheetApp.newFilterCriteria().whenTextStartsWith('A').build(), );

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const anchorCell = spreadsheet.getSheets()[0].getRange('A1'); const dataSource = spreadsheet.getDataSources()[0];  const dataSourceTable =     anchorCell.createDataSourceTable(dataSource)         .addColumns('dataColumnA', 'dataColumnB', 'dataColumnC')         .addSortSpec('dataColumnA', true)    // ascending=true         .addSortSpec('dataColumnB', false);  // ascending=false

// 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 the range A1:C6. const range = sheet.getRange('A1:C6');  // Creates a developer metadata finder to search for metadata in the scope of // this range. const developerMetaDataFinder = range.createDeveloperMetadataFinder();  // Logs information about the developer metadata finder to the console. const developerMetaData = developerMetaDataFinder.find()[0]; console.log(developerMetaData.getKey()); console.log(developerMetaData.getValue()); console.log(developerMetaData.getVisibility().toString());

const ss = SpreadsheetApp.getActiveSheet(); const range = ss.getRange('A1:C20');  // Creates a new filter and applies it to the range A1:C20 on the active sheet. function createFilter() {   range.createFilter(); } // Gets the filter and applies criteria that only shows cells that aren't empty. function getFilterAddCriteria() {   const filter = range.getFilter();   const criteria =       SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();   filter.setColumnFilterCriteria(2, criteria); }

// 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 as a range in order to place the pivot table. const range = sheet.getRange('A1');  // Gets the range of the source data for the pivot table. const dataRange = sheet.getRange('E12:G20');  // Creates an empty pivot table from the specified source data. const pivotTable = range.createPivotTable(dataRange);  // Logs the values from the pivot table's source data to the console. console.log(pivotTable.getSourceDataRange().getValues());

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; const range = sheet.getActiveRange();  // Creates  a text finder for the range. const textFinder = range.createTextFinder('dog');  // Returns the first occurrence of 'dog'. const firstOccurrence = textFinder.findNext();  // Replaces the last found occurrence of 'dog' with 'cat' and returns the number // of occurrences replaced. const numOccurrencesReplaced = textFinder.replaceWith('cat');

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('A1:D10'); range.deleteCells(SpreadsheetApp.Dimension.COLUMNS);

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; const range = sheet.getActiveRange();  // All row and column groups within the range are expanded. range.expandGroups();

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0]; const range = sheet.getRange(1, 1, 2, 5);  // Logs "A1:E2" Logger.log(range.getA1Notation());

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const cell = sheet.getRange('B5'); Logger.log(cell.getBackground());

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const cell = sheet.getRange('B5'); Logger.log(cell.getBackgroundObject().asRgbColor().asHexString());

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('B5:C6'); const bgColors = range.getBackgroundObjects(); for (const i in bgColors) {   for (const j in bgColors[i]) {     Logger.log(bgColors[i][j].asRgbColor().asHexString());   } }

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('B5:C6'); const bgColors = range.getBackgrounds(); for (const i in bgColors) {   for (const j in bgColors[i]) {     Logger.log(bgColors[i][j]);   } }

// 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 a range. const range = sheet.getRange('A1:K50');  // Gets the banding info for the range. const bandings = range.getBandings();  // Logs the second row color for each banding to the console. for (const banding of bandings) {   console.log(banding.getSecondRowColor()); }

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('B2:D4');  // The row and column here are relative to the range // getCell(1,1) in this code returns the cell at B2 const cell = range.getCell(1, 1); Logger.log(cell.getValue());

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0];  const range = sheet.getRange('B2:D4'); // Logs "2.0" Logger.log(range.getColumn());

// Assume the active spreadsheet is blank. const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0]; sheet.getRange('C2').setValue(100); sheet.getRange('B3').setValue(100); sheet.getRange('D3').setValue(100); sheet.getRange('C4').setValue(100); // Logs "B2:D4" Logger.log(sheet.getRange('C3').getDataRegion().getA1Notation());

// Assume the active spreadsheet is blank. const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0]; sheet.getRange('C2').setValue(100); sheet.getRange('B3').setValue(100); sheet.getRange('D3').setValue(100); sheet.getRange('C4').setValue(100); // Logs "C2:C4" Logger.log(     sheet.getRange('C3')         .getDataRegion(SpreadsheetApp.Dimension.ROWS)         .getA1Notation(), ); // Logs "B3:D3" Logger.log(     sheet.getRange('C3')         .getDataRegion(SpreadsheetApp.Dimension.COLUMNS)         .getA1Notation(), );

// 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 Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets the range A1 on Sheet1. const range = sheet.getRange('A1');  // Gets the data source formula from cell A1. const dataSourceFormula = range.getDataSourceFormula();  // Gets the formula. const formula = dataSourceFormula.getFormula();  // Logs the formula. console.log(formula);

// 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 Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets the range A1:B5 on Sheet1. const range = sheet.getRange('A1:B5');  // Gets an array of the data source formulas in the range A1:B5. const dataSourceFormulas = range.getDataSourceFormulas();  // Logs the first formula in the array. console.log(dataSourceFormulas[0].getFormula());

// 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 Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets the range A1:G50 on Sheet1. const range = sheet.getRange('A1:G50');  // Gets an array of the data source pivot tables in the range A1:G50. const dataSourcePivotTables = range.getDataSourcePivotTables();  // Logs the last time that the first pivot table in the array was refreshed. console.log(dataSourcePivotTables[0].getStatus().getLastRefreshedTime());

// 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 Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets the range A1:G50 on Sheet1. const range = sheet.getRange('A1:G50');  // Gets the first data source table in the range A1:G50. const dataSourceTable = range.getDataSourceTables()[0];  // Logs the time of the last completed data execution on the data source table. console.log(dataSourceTable.getStatus().getLastExecutionTime());

// 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 Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets the range A1:B7 on Sheet1. const range = sheet.getRange('A1:B7');  // Gets the range A1:B7 as a data table. The values in each column must be of // the same type. const datatable = range.getDataTable();  // Uses the Charts service to build a bar chart from the data table. // This doesn't build an embedded chart. To do that, use // sheet.newChart().addRange() instead. const chart = Charts.newBarChart()                   .setDataTable(datatable)                   .setOption('title', 'Your Chart Title Here')                   .build();

const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheets()[0]; const range = sheet.getRange('A1:B7');  // Calling this method with "true" sets the first line to be the title of the // axes const datatable = range.getDataTable(true);  // Note that this doesn't build an EmbeddedChart, so you can't just use // Sheet#insertChart(). To do that, use sheet.newChart().addRange() instead. const chart = Charts.newBarChart()                   .setDataTable(datatable)                   .setOption('title', 'Your Title Here')                   .build();

// Log information about the data validation rule for cell A1. const cell = SpreadsheetApp.getActive().getRange('A1'); const rule = cell.getDataValidation(); if (rule != null) {   const criteria = rule.getCriteriaType();   const args = rule.getCriteriaValues();   Logger.log('The data validation rule is %s %s', criteria, args); } else {   Logger.log('The cell does not have a data validation rule.'); }

// Change existing data validation rules that require a date in 2013 to require // a date in 2014. const oldDates = [new Date('1/1/2013'), new Date('12/31/2013')]; const newDates = [new Date('1/1/2014'), new Date('12/31/2014')]; const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()); const rules = range.getDataValidations();  for (let i = 0; i < rules.length; i++) {   for (let j = 0; j < rules[i].length; j++) {     const rule = rules[i][j];      if (rule != null) {       const criteria = rule.getCriteriaType();       const args = rule.getCriteriaValues();        if (criteria === SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN &&           args[0].getTime() === oldDates[0].getTime() &&           args[1].getTime() === oldDates[1].getTime()) {         // Create a builder from the existing rule, then change the dates.         rules[i][j] = rule.copy().withCriteria(criteria, newDates).build();       }     }   } } range.setDataValidations(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', );  // Gets Sheet1 by its name. const sheet = ss.getSheetByName('Sheet1');  // Gets row 2 on Sheet1. const range = sheet.getRange('2:2');  // Adds metadata to row 2. range.addDeveloperMetadata('NAME', 'GOOGLE');  // Logs the metadata to console. for (const metadata of range.getDeveloperMetadata()) {   console.log(`${metadata.getKey()}: ${metadata.getValue()}`); }