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?

Answer 1

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]