147
votes

I want to set text or number in Google Sheet from script.

I want to set Hello or number 9 in cell F2. I found this code so far:

SpreadsheetApp.getActiveRange().setValue('hello');

but that doesn't specify which cell.

3
may I suggest that you read the basics of appscript by trying the tutorials ?Serge insas

3 Answers

237
votes

The following code does what is required

function doTest() {
  SpreadsheetApp.getActiveSheet().getRange('F2').setValue('Hello');
}
48
votes

Use setValue method of Range class to set the value of particular cell.

function storeValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // ss is now the spreadsheet the script is associated with
  var sheet = ss.getSheets()[0]; // sheets are counted starting from 0
  // sheet is the first worksheet in the spreadsheet
  var cell = sheet.getRange("B2"); 
  cell.setValue(100);
}

You can also select a cell using row and column numbers.

var cell = sheet.getRange(2, 3); // here cell is C2

It's also possible to set value of multiple cells at once.

var values = [
  ["2.000", "1,000,000", "$2.99"]
];

var range = sheet.getRange("B2:D2");
range.setValues(values);
19
votes

Solution : SpreadsheetApp.getActiveSheet().getRange('F2').setValue('hello')

Explanation :

Setting value in a cell in spreadsheet to which script is attached

SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getRange(RANGE).setValue(VALUE);

Setting value in a cell in sheet which is open currently and to which script is attached

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(RANGE).setValue(VALUE);

Setting value in a cell in some spreadsheet to which script is NOT attached (Destination sheet name known)

SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME).getRange(RANGE).setValue(VALUE);

Setting value in a cell in some spreadsheet to which script is NOT attached (Destination sheet position known)

SpreadsheetApp.openById(SHEET_ID).getSheets()[POSITION].getRange(RANGE).setValue(VALUE);

These are constants, you must define them yourself

SHEET_ID

SHEET_NAME

POSITION

VALUE

RANGE

By script attached to a sheet I mean that script is residing in the script editor of that sheet. Not attached means not residing in the script editor of that sheet. It can be in any other place.