CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user635600
user635600

Reputation:

How to get the correct range to set the value to a cell?

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.

Upvotes: 160

Views: 440878

Answers (3)

Umair Mohammad
Umair Mohammad

Reputation: 4635

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.

Upvotes: 27

sudo bangbang
sudo bangbang

Reputation: 28169

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);

Upvotes: 55

megabyte1024
megabyte1024

Reputation: 8650

The following code does what is required

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

Upvotes: 258

Related Questions