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