Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to write in cells in GoogleSpreadSheet using app Script?

and sorry for the stupid question.

I know how to program on VBA for Excel, but I´m having a hard time doing the simplest stuff in Google Spreadsheet and I can´t find any good tutorials on-line.

My question is regarding cells. In VBA to write in a cells I would do:

cells(1,1) = 2 //that would write the number 2 in the first row of the first column  

In VBA I can also assign a cell to a variable, like:

dim something as long
something = cells(1,1)

How can I apply the same principles in google SpreadSheet?

Thank you very much!

I´ve tried what you guys suggested. And now I have a follow up question.

I`m trying to modify the cells in a specific range. I´ve tried this code:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var board = sheet.getRange(2,2,9,9);
board[0][0].setValue(2);

But apparently I can´t use array notation. I´m not sure if .setValeu or .getRange are the problem. But is there a way I can use array notation to chance a cell?

Thanks Again!

like image 734
user3347814 Avatar asked Apr 06 '14 17:04

user3347814


People also ask

How do you enter within a cell in Google Sheets app?

No matter which applications, whether it's Android or desktop, you use. You can use the =CHAR(10) formula to start new a line within a cell in Sheets.

How do you assign a value to a cell in Google script?

Use setValue method of Range class to set the value of particular cell. You can also select a cell using row and column numbers. It's also possible to set value of multiple cells at once.


2 Answers

Try following:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange(1,1);
cell.setValue(2);
like image 90
swimmingwood Avatar answered Oct 19 '22 08:10

swimmingwood


It's worthwhile looking at the docs for getRange.

getRange(row, column, numRows, numColumns)

Returns the range with the top left cell at the given coordinates with the given number of rows and columns.

You can therefore update multiple cells with an array like this:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, numRows, numColumns).setValues(data);
SpreadsheetApp.flush();
like image 43
AlexG Avatar answered Oct 19 '22 07:10

AlexG