Apps Script Course: Sheets and Cells

After retrieving and modifying the content of cells, we will now look into other actions you can perform in relation to sheets and cells.

Sheet

In the previous pages, we often used this line of code:

const sheet = SpreadsheetApp.getActiveSheet();

An object of type Sheet is assigned here to the constant sheet, which allows us to access plenty of methods to act on this sheet.

Here are some examples...

activate: activates a sheet:

const sheet2 = SpreadsheetApp.getActive().getSheetByName('Sheet 2');
sheet2.activate();

appendRow: inserts a row following the data of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow([1, 2, 3]);

clear: completely deletes the content and formatting of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
sheet.clear();

clearContents: deletes the content of the sheet (while leaving the formatting):

const sheet = SpreadsheetApp.getActiveSheet();
sheet.clearContents();

deleteRow: deletes an entire row of the sheet (here, row 17):

const sheet = SpreadsheetApp.getActiveSheet();
sheet.deleteRow(17);

getActiveCell: returns the active cell of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
const activeCell = sheet.getActiveCell();

console.log(activeCell.getValue()); // Displays the value of the active cell

getActiveRange: returns the active cell range of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
const activeRange = sheet.getActiveRange();

console.log(activeRange.getValues()); // Displays the values of the cells in the active range

getDataRange: returns the cell range of the sheet containing data:

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getDataRange();

getName: returns the name of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
const sheetName = sheet.getName();

console.log(sheetName); // Displays the name of the sheet

getRange: returns a cell or a range of cells of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A1:B4');

getSheetId: returns the ID of the sheet:

const sheet = SpreadsheetApp.getActiveSheet();
const sheetId = sheet.getSheetId();

console.log(sheetId); // Displays the ID of the sheet

Cells

Just like the sheet, cells are objects that have a long list of available methods. Here are a few of them...

activate: activates (selects) the cell:

const cellC4 = SpreadsheetApp.getActiveSheet().getRange('C4');
cellC4.activate();

clear: completely removes the content and formatting of the cell:

const cellC4 = SpreadsheetApp.getActiveSheet().getRange('C4');
cellC4.clear();

clearContent: removes the content of the cell (leaving the formatting):

const cellC4 = SpreadsheetApp.getActiveSheet().getRange('C4');
cellC4.clearContent();

getA1Notation: retrieves a description of the cell or range (in A1 format):

const activeCell = SpreadsheetApp.getActiveSheet().getActiveCell();
const description = activeCell.getA1Notation();

console.log(description); // If the active cell is C4, displays: C4

getValue: retrieves the value of the cell:

const cellC4 = SpreadsheetApp.getActiveSheet().getRange('C4');
const value = cellC4.getValue();

console.log(value); // Displays the value of cell C4

getValues: retrieves the values of the cell range:

const range = SpreadsheetApp.getActiveSheet().getRange('C4:D7');
const array = range.getValues();

console.log(array); // Displays the values of the cells in an array

offset: returns the cell or cell range taking into account an offset:

const cellC4 = SpreadsheetApp.getActiveSheet().getRange('C4');
const cellC5 = cellC4.offset(1, 0);

setBackground: applies a background color (in CSS format) to the cells:

const range = SpreadsheetApp.getActiveSheet().getRange('A1:E1');
range.setBackground('#455fbb');

setBackgroundRGB: applies a background color (in RGB format) to the cells:

const range = SpreadsheetApp.getActiveSheet().getRange('A1:E1');
range.setBackgroundRGB(48, 163, 146);

setBorder: applies borders to the cells (here, complete borders, dashed and colored):

const range = SpreadsheetApp.getActiveSheet().getRange('A2:E10');
range.setBorder(true, true, true, true, true, true, '#455fbb', SpreadsheetApp.BorderStyle.DASHED);

setFontColor: applies a color (in CSS format) to the text of the cells:

const range = SpreadsheetApp.getActiveSheet().getRange('A2:E10');
range.setFontColor('#455fbb');

setFontSize: sets the text size of the cells:

const range = SpreadsheetApp.getActiveSheet().getRange('A2:E10');
range.setFontSize(14);

setFormula: inserts a formula into the cell:

const cellTotal = SpreadsheetApp.getActiveSheet().getRange('A10');
cellTotal.setFormula('=SUM(A2:A9)');

setValue: sets the value of the cell:

const cellC4 = SpreadsheetApp.getActiveSheet().getRange('C4');
cellC4.setValue('New value');

setValues: sets the values of the cell range:

const range = SpreadsheetApp.getActiveSheet().getRange('A2:C3');
range.setValues([['a2', 'b2', 'c2'], ['a3', 'b3', 'c3']]);

sort: sorts the cell range (here, based on the first column):

const range = SpreadsheetApp.getActiveSheet().getRange('A2:C10');
range.sort(1);