Can't Pull Cell Data From Other Sheet with Apps Script

May 6, 2024

I need to pull data from a cell in one google sheet (our log) and use that information to rename the current sheet.

In the code below I pull the log sheet and I need. I can access the document name just fine, but when I call a specific range the output I always get is "Range" and not the data in the relevant cell.

I can’t figure out why this is since the sheet is certainly correct.

function getLog11() { 
  //logFile is the document and sheet with data I need
  var logFile = SpreadsheetApp.openById('1GJP63dcEEyBVTMPtI1rZa6M9ppWX81_B3Xlnf06Ajbw').getSheetByName("Mainbox");
  //this puts the data where it needs to go
  SpreadsheetApp.openById('17yZgrKVkyMbYWrOl8mCqUyRCr3zTy6PB2nAR2QNs33w').getRange("D6").setValue(logFile.getRange("D44"));
  //output test
  Logger.log(logFile.getRange("D44"));
  

}

>Solution :

This works for me. You just neet to use getValue() on the ranges. The ranges don’t return values by themselves

function getLog11() { 
  var sh = SpreadsheetApp.openById(gobj.globals.test1id).getSheetByName("Sheet1");
  SpreadsheetApp.openById(gobj.globals.test2id).getRange("D6").setValue(sh.getRange("D44").getValue());
  Logger.log(sh.getRange("D44").getValue());
}

gobj.globals.test1id and gobj.globals.test2id are just spreadsheet id’s for two different spreadsheets using my global variables object. And by the way Test1 and Test2 are different sheets from the sheet where the script is running