CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgetvalue
Lauren Savage
Lauren Savage

Reputation: 1

getValues and getBackgrouds not returning data in Google Apps Script

I'm trying to create a code that checks to see if a cell has a checkbox checked (or unchecked) and then copies data from a corresponding cell on that row onto another "Summary" sheet. The "if checked then copy data" portion has not been included below.

The problem I'm having is that neither getValues nor getBackgound seems to be returning data.

When I run my code, the Logger shows that every checkbox value is "false" (even though some are checked and I have their values set to 1 (checked) and 0 (unchecked) using the data validation in the spreadsheet settings.

If I switch to getBackgrounds, the Logger shows that every cell's background is white "#ffffff", even though the cells are red.

[[I want my code to iterate over every sheet, row, and column. It seems to be iterating fine, but not retrieving the data.]]

What I am doing wrong? Here's my full code:

function MAPSuccess() {
var app = SpreadsheetApp;
var ss = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/1UUdiLsz2OvKdqnr2KB0Tx74gzrGx0m4XUA5oNMUB06k/edit');
var allsheets = ss.getSheets();
  var s = ss.getNumSheets();
  var master = app.getActiveSpreadsheet().getSheetByName("Master");
  var masterLastRow = master.getLastRow();
  

  for (var s = 2; s < allsheets.length; s++) { 
var sheet = app.getActiveSpreadsheet().getActiveSheet();
var r = sheet.getCurrentCell().getRow();    
var lastrow = sheet.getLastRow();
var lastcol = sheet.getLastColumn();
var temail = sheet.getRange(1,2);
var tSJCOE = sheet.getRange(2,2);
var c = sheet.getActiveCell().getColumn();

  for(var r = 4;r<=lastrow;r++){

var name= sheet.getRange(r,1).getValue();
var lname= sheet.getRange(r,2).getValue();
var email= sheet.getRange(r,3).getValue();
var pemail= sheet.getRange(r,4).getValue();
    var info = [
      [name, lname, email, pemail]
      ]
var range = sheet.getRange(r,1,1,lastcol);
var rowValues = range.getValues();
   
var cell = [
  [s,r,c]
  ];
    Logger.log(rowValues);
    Logger.log(name);
//    Logger.log(checkRow);
    Logger.log(cell);

Upvotes: 0

Views: 978

Answers (1)

Cameron Roberts
Cameron Roberts

Reputation: 7377

It looks like you are always performing your actions on the active sheet, instead of accessing the sheets in "allsheets".

var sheet = app.getActiveSpreadsheet().getActiveSheet();

should be 

var sheet = allsheets[s];

You may also be misusing "getCurrentCell" on the next line, since that will return whichever cell is currently selected in the spreadsheet, of which there is always only one, regardless of which sheet you are accessing.

Upvotes: 1

Related Questions