Transferring cell data in Google Script?

Question

I'm trying to make a script for my Google spreadsheet.

What I want is for the script to check if a cell is empty in one sheet, and if its not, I want it to transfer that data into the cell of another sheet. Whenever I try this, it keeps saying "Cannot call method "getRange()" of undefined."

This is all I got so far:

function timesheets(){
  var ss = SpreadsheetApp.openById("1iL2Didv_YOLpTad5WdR3kZSjLOLTSQk_yV-gQhh4RXs");
  if("ss!C2" != ""){
    var Tasks = ss.getRange("C2").getValue();
  }
}

I'm trying to create a new function instead of using the functions on Google sheets. Would I have to include the names of both the spreadsheets?

would ` range.getCell(row,column) ` be what you are looking for? (eg range.getCell(2,1) for Row 2, Column A.) — TheDarkTurtle
I usually use sheet.getRange(row,column), however A1 notation will work. So range.getRange(2,3) = range.getRange("C2"). Looking at .getRange and .getCell; sheet.getRange returns the Class Range relative to the sheet. range.getCell() returns the Class Range relative to the "range" which you will have to have already got. If you are getting 1 cell, they will both give you the same value back but it may be refer to a different cell on the sheet depending on what .getCell considers the "range" to be. — Niccolo

Niccolo Niccolo · Accepted Answer · 2014-08-13T13:20:23

You are accessing a spreadsheet, you need to get down to the sheet level first.

I suggest getting the script to work within the same spreadsheet before you try to talk from one to another.

function timesheets(){
  var ss = SpreadsheetApp.openById("1iL2Didv_YOLpTad5WdR3kZSjLOLTSQk_yV-gQhh4RXs");
  var sheet = ss.getActiveSheet();
  if("sheet!C2" != ""){
    var Tasks = sheet.getRange("C2").getValue();
  }
}

I'm trying to create a new function instead of using the functions on Google sheets. Would I have to include the names of both the spreadsheets?

The spreadsheet that has the code in it (target) needs to know the Id of the sheet that it is looking at for data (source). When you want to write the value to the (target) spreadsheet, you need to give it a variable to write to;

var sheetTarger = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

SECTION UNDERNEATH IS POST-EDIT ##

I created two spreadsheets.

One called source where I wrote the word "hello" into cell C2

In one called "target" I placed the following code

function timesheets(){
  var ss = SpreadsheetApp.openById("*********************");
  var sheet = ss.getActiveSheet();
  if("sheet!C2" != ""){
    var Tasks = sheet.getRange("C2").getValue();
  }
  var sheetTargert = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheetTargert.getRange("C2").setValue(Tasks);
}

I replaced "*****************" with the Id from source.

Within the script editor I ran the function timesheets()

The value "hello" appeared in the target cell C2

In the script editor I went to

View>Execution transcripts

This produced the following

[14-08-13 15:28:09:405 BST] Starting execution
[14-08-13 15:28:09:787 BST] SpreadsheetApp.openById([**************]) [0.366 seconds]
[14-08-13 15:28:09:891 BST] Spreadsheet.getActiveSheet() [0 seconds]
[14-08-13 15:28:09:975 BST] Sheet.getRange([C2]) [0.083 seconds]
[14-08-13 15:28:10:058 BST] Range.getValue() [0.082 seconds]
[14-08-13 15:28:10:058 BST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[14-08-13 15:28:10:139 BST] Spreadsheet.getActiveSheet() [0.08 seconds]
[14-08-13 15:28:10:139 BST] Sheet.getRange([10, 10]) [0 seconds]
[14-08-13 15:28:10:140 BST] Range.setValue([hello]) [0 seconds]
[14-08-13 15:28:10:386 BST] Sheet.getRange([C2]) [0.245 seconds]
[14-08-13 15:28:10:387 BST] Range.setValue([hello]) [0 seconds]
[14-08-13 15:28:10:559 BST] Execution succeeded [0.967 seconds total runtime]

Transferring cell data in Google Script?

1 Answers

SECTION UNDERNEATH IS POST-EDIT ##