Apps Script Course: Modifying Cell Values

We will now look into modifying the content of a cell or a range of cells, followed by 2 simple practical examples (the counter and adding data sequentially in another sheet).

Modify the Value of a Cell

The modification is very similar to retrieving a cell's value, as instead of using the getValue method, we will use setValue here.

In this example, the code enters the value "1234" in cell A1:

const sheet = SpreadsheetApp.getActiveSheet();
const cell = sheet.getRange('A1');
cell.setValue(1234); // Enters the value "1234" in cell A1

Modify the Values of a Range of Cells

To modify an entire range, use the setValues method (to which you must provide an array containing one array per row, like the data returned by getValues).

In this example, the code enters the values 1 to 5 in cells A1 to E1:

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A1:E1');
range.setValues([[1, 2, 3, 4, 5]]); // Enters the values 1 to 5 in cells A1 to E1

To better visualize the "grid," you can display the array as follows:

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A1:E1');
const array = [
  [1, 2, 3, 4, 5]
];
range.setValues(array); // Enters the values 1 to 5 in cells A1 to E1

Another example to modify a range of 2 columns and 5 rows:

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('A1:B5');
const array = [
  [1, 11],
  [2, 22],
  [3, 33],
  [4, 44],
  [5, 55]
];
range.setValues(array); // Enters the values in cells A1 to B5

To obtain:

Create a Counter

The goal here is to create a counter in A2:

Start by copying the test function and complete it (before moving to the solution a bit further down):

• retrieve the value of cell A2
• modify the value of cell A2 to its own value + 1
• (optional) assign the test function to a shape or an image (as at the very beginning of this course)

function test() {

  const sheet = SpreadsheetApp.getActiveSheet();

  // To be completed...

}

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

The commented solution:

function test() {

  // Active sheet
  const sheet = SpreadsheetApp.getActiveSheet();

  // Cell A2
  const counterCell = sheet.getRange('A2');

  // Value of cell A2 + 1
  const counter = counterCell.getValue() + 1;

  // Change the value of cell A2
  counterCell.setValue(counter);
}

Add a Row Sequentially

To add a range of cells sequentially in another sheet, there is a very convenient method, appendRow.

To start, here is a simple example that inserts an array at the end of the sheet "Sheet2":

const sheet2 = SpreadsheetApp.getActive().getSheetByName('Sheet2');
sheet2.appendRow(['Value 1', 'Value 2', 'Value 3']);

Now complete the following function so that it copies row 1 of "Sheet1" sequentially into "Sheet2":

function test() {

  const ss = SpreadsheetApp.getActive(); // To avoid executing "getActive()" twice
  const sheet1 = ss.getSheetByName('Sheet1');
  const sheet2 = ss.getSheetByName('Sheet2');

  // To be completed...

}

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

The commented solution:

function test() {

  const ss = SpreadsheetApp.getActive(); // To avoid executing "getActive()" twice
  const sheet1 = ss.getSheetByName('Sheet1');
  const sheet2 = ss.getSheetByName('Sheet2');

  // Cells of row 1
  const cells = sheet1.getRange('1:1');

  // Values of the cells of row 1
  const array = cells.getValues();

  // Row 1 of the array
  const rowArray = array[0];

  // Inserting values
  sheet2.appendRow(rowArray);
}

Note that you don't necessarily need to go through all these constants, if you feel comfortable, you can create shorter versions (although a bit less "readable"):

function test() {

  const ss = SpreadsheetApp.getActive(); // To avoid executing "getActive()" twice
  const sheet1 = ss.getSheetByName('Sheet1');
  const sheet2 = ss.getSheetByName('Sheet2');

  // Copies values from row 1 of "Sheet1" sequentially into "Sheet2"
  sheet2.appendRow(sheet1.getRange('1:1').getValues()[0]);
}