Class Range

constsheet=SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
constrange=sheet.getRange('A1:D10');
range.activate();
constselection=sheet.getSelection();
// Current cell: A1
constcurrentCell=selection.getCurrentCell();
// Active Range: A1:D10
constactiveRange=selection.getActiveRange();

// Gets the first sheet of the spreadsheet.
constsheet=SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
// Gets the cell B5 and sets it as the active cell.
constrange=sheet.getRange('B5');
constcurrentCell=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 on the sheet.
constrange=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.
constdeveloperMetaData=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 on Sheet1.
constrange=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.
constdeveloperMetaData=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 of Sheet1.
constrange=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.
constdeveloperMetaData=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 on the sheet.
constrange=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.
constdeveloperMetaData=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 on the sheet.
constrange=sheet.getRange('2:2');
// Applies column banding to row 2.
constcolBanding=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 on the sheet.
constrange=sheet.getRange('2:2');
// Applies the INDIGO color banding theme to the columns in row 2.
constcolBanding=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets rows 12-22 on the sheet.
constrange=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.
constcolBanding=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets rows 1-30 on Sheet1.
constrange=sheet.getRange('1:30');
// Applies row banding to rows 1-30.
range.applyRowBanding();
// Gets the hex color of the second banded row.
constsecondRowColor=
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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets rows 1-30 on Sheet1.
constrange=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.
constsecondRowColor=
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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets rows 1-30 on Sheet1.
constrange=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);

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

constsheet=SpreadsheetApp.getActiveSheet();
// A1:A20 has values [1, 2, 3, ... 20].
// B1:B4 has values [1/1/2017, 1/2/2017, ...]
constsourceRange=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:C6 on Sheet1.
constrange=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:C6 on Sheet1.
constrange=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'.
constrange=SpreadsheetApp.getActive().getRange('A1:B10');
range.check();

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=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
});

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

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

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

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

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

constss=SpreadsheetApp.getActiveSpreadsheet();
constsource=ss.getSheets()[0];
constrange=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);

constss=SpreadsheetApp.getActiveSpreadsheet();
constsource=ss.getSheets()[0];
constdestination=ss.getSheets()[1];
constrange=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.
constsheet=SpreadsheetApp.getActiveSheet();
constrangeToCopy=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.
constsheet=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.
constsheet=SpreadsheetApp.getActiveSheet();
sheet.getRange('A:E').copyTo(sheet.getRange('F1'),{contentsOnly:true});

constss=SpreadsheetApp.getActiveSpreadsheet();
constsource=ss.getSheets()[0];
constrange=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);

constss=SpreadsheetApp.getActiveSpreadsheet();
constsource=ss.getSheets()[0];
constdestination=ss.getSheets()[1];
constrange=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);

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

constspreadsheet=SpreadsheetApp.getActiveSpreadsheet();
constanchorCell=spreadsheet.getSheets()[0].getRange('A1');
constdataSource=spreadsheet.getDataSources()[0];
constdataSourceTable=
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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:C6.
constrange=sheet.getRange('A1:C6');
// Creates a developer metadata finder to search for metadata in the scope of
// this range.
constdeveloperMetaDataFinder=range.createDeveloperMetadataFinder();
// Logs information about the developer metadata finder to the console.
constdeveloperMetaData=developerMetaDataFinder.find()[0];
console.log(developerMetaData.getKey());
console.log(developerMetaData.getValue());
console.log(developerMetaData.getVisibility().toString());

constss=SpreadsheetApp.getActiveSheet();
constrange=ss.getRange('A1:C20');
// Creates a new filter and applies it to the range A1:C20 on the active sheet.
functioncreateFilter(){
range.createFilter();
}
// Gets the filter and applies criteria that only shows cells that aren't empty.
functiongetFilterAddCriteria(){
constfilter=range.getFilter();
constcriteria=
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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets cell A1 as a range in order to place the pivot table.
constrange=sheet.getRange('A1');
// Gets the range of the source data for the pivot table.
constdataRange=sheet.getRange('E12:G20');
// Creates an empty pivot table from the specified source data.
constpivotTable=range.createPivotTable(dataRange);
// Logs the values from the pivot table's source data to the console.
console.log(pivotTable.getSourceDataRange().getValues());

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

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

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

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

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

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

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B5:C6');
constbgColors=range.getBackgroundObjects();
for(constiinbgColors){
for(constjinbgColors[i]){
Logger.log(bgColors[i][j].asRgbColor().asHexString());
}
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B5:C6');
constbgColors=range.getBackgrounds();
for(constiinbgColors){
for(constjinbgColors[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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Sets a range.
constrange=sheet.getRange('A1:K50');
// Gets the banding info for the range.
constbandings=range.getBandings();
// Logs the second row color for each banding to the console.
for(constbandingofbandings){
console.log(banding.getSecondRowColor());
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=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
constcell=range.getCell(1,1);
Logger.log(cell.getValue());

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

// Assume the active spreadsheet is blank.
constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=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.
constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=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.
constss=SpreadsheetApp.openById('abc123456');
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1 on Sheet1.
constrange=sheet.getRange('A1');
// Gets the data source formula from cell A1.
constdataSourceFormula=range.getDataSourceFormula();
// Gets the formula.
constformula=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.
constss=SpreadsheetApp.openById('abc123456');
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:B5 on Sheet1.
constrange=sheet.getRange('A1:B5');
// Gets an array of the data source formulas in the range A1:B5.
constdataSourceFormulas=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.
constss=SpreadsheetApp.openById('abc123456');
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:G50 on Sheet1.
constrange=sheet.getRange('A1:G50');
// Gets an array of the data source pivot tables in the range A1:G50.
constdataSourcePivotTables=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.
constss=SpreadsheetApp.openById('abc123456');
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:G50 on Sheet1.
constrange=sheet.getRange('A1:G50');
// Gets the first data source table in the range A1:G50.
constdataSourceTable=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.
constss=SpreadsheetApp.openById('abc123456');
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets the range A1:B7 on Sheet1.
constrange=sheet.getRange('A1:B7');
// Gets the range A1:B7 as a data table. The values in each column must be of
// the same type.
constdatatable=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.
constchart=Charts.newBarChart()
.setDataTable(datatable)
.setOption('title','Your Chart Title Here')
.build();

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('A1:B7');
// Calling this method with "true" sets the first line to be the title of the
// axes
constdatatable=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.
constchart=Charts.newBarChart()
.setDataTable(datatable)
.setOption('title','Your Title Here')
.build();

// Log information about the data validation rule for cell A1.
constcell=SpreadsheetApp.getActive().getRange('A1');
construle=cell.getDataValidation();
if(rule!=null){
constcriteria=rule.getCriteriaType();
constargs=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.
constoldDates=[newDate('1/1/2013'),newDate('12/31/2013')];
constnewDates=[newDate('1/1/2014'),newDate('12/31/2014')];
constsheet=SpreadsheetApp.getActiveSheet();
constrange=sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
construles=range.getDataValidations();
for(leti=0;i < rules.length;i++){
for(letj=0;j < rules[i].length;j++){
construle=rules[i][j];
if(rule!=null){
constcriteria=rule.getCriteriaType();
constargs=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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets row 2 on Sheet1.
constrange=sheet.getRange('2:2');
// Adds metadata to row 2.
range.addDeveloperMetadata('NAME','GOOGLE');
// Logs the metadata to console.
for(constmetadataofrange.getDeveloperMetadata()){
console.log(`${metadata.getKey()}: ${metadata.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.
constss=SpreadsheetApp.openByUrl(
'https://docs.google.com/spreadsheets/d/abc123456/edit',
);
// Gets Sheet1 by its name.
constsheet=ss.getSheetByName('Sheet1');
// Gets cell A30 and sets its value to 'Test code.'
constcell=sheet.getRange('A30');
cell.setValue('Test code');
// Gets the value and logs it to the console.
console.log(cell.getDisplayValue());

// The code below gets the displayed values for the range C2:G8
// in the active spreadsheet. Note that this is a JavaScript array.
constvalues=
SpreadsheetApp.getActiveSheet().getRange(2,3,6,4).getDisplayValues();
Logger.log(values[0][0]);

constss=SpreadsheetApp.getActiveSheet();
constrange=ss.getRange('A1:C20');
// Gets the existing filter on the sheet that the given range belongs to.
constfilter=range.getFilter();

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
Logger.log(range.getFontColorObject().asRgbColor().asHexString());

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
constresults=range.getFontColorObjects();
for(constiinresults){
for(constjinresults[i]){
Logger.log(results[i][j].asRgbColor().asHexString());
}
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
constresults=range.getFontFamilies();
for(constiinresults){
for(constjinresults[i]){
Logger.log(results[i][j]);
}
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
Logger.log(range.getFontFamily());

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
Logger.log(range.getFontLine());

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
constresults=range.getFontLines();
for(constiinresults){
for(constjinresults[i]){
Logger.log(results[i][j]);
}
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
Logger.log(range.getFontSize());

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
constresults=range.getFontSizes();
for(constiinresults){
for(constjinresults[i]){
Logger.log(results[i][j]);
}
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
Logger.log(range.getFontStyle());

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
constresults=range.getFontStyles();
for(constiinresults){
for(constjinresults[i]){
Logger.log(results[i][j]);
}
}

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
Logger.log(range.getFontWeight());

constss=SpreadsheetApp.getActiveSpreadsheet();
constsheet=ss.getSheets()[0];
constrange=sheet.getRange('B2:D4');
constresults=range.getFontWeights();
for(constiinresults){
for(constjinresults[i]){
Logger.log(results[i][j]);
}
}