Appscript Google sheets returns null

Question

I have 2 sheet on my spreadsheet. My code works with first sheet but when i try with second sheet it returns null. I can't understand why. Also i checked realtime requests it looks returns my data but my sidebar value null still.

function getAllRowsData() {
  try {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getActiveSheet();
    
    // Get the spreadsheet name
    var spreadsheetName = sheet.getName();
    
    // Get the values of all rows
    var allRows = sheet.getDataRange().getValues();
        
    // Get the column names from the first row
    var columnNames = allRows[0];
    
    // Get the data rows (excluding the header row)
    var dataRows = allRows.slice(1);
    
    var result = { spreadsheetName: spreadsheetName, columns: columnNames, rows: dataRows };
    Logger.log(result);
    return result;
  } catch (error) {
    Logger.log("An error occurred: " + error.message);
    return null;
  }
}

function getActiveCellValue() {
            google.script.run.withSuccessHandler(setData).withFailureHandler(function(e){console.error(e)}).withLogger(function(e){console.warn("The following log was generated:"+e)}).getAllRowsData();
        }

I wanted to get my data but it returns null

Answer 1

The function getAllRowsData() is designed to work with the active sheet getActiveSheet(), which means it will only work with the sheet that is currently active i.e., the one you are viewing in the spreadsheet UI. If you're trying to access data from the second sheet but it's not set as the active sheet at the time of running your script, getAllRowsData() will not return the expected data from the second sheet.

Modify getAllRowsData() to accept sheets by name:

function getAllRowsData(sheetName) {
    try {
        var ss = SpreadsheetApp.getActive();
        var sheet = ss.getSheetByName(sheetName);
        if (!sheet) {
            Logger.log("Sheet not found: " + sheetName);
            return null;
        }
        var spreadsheetName = sheet.getName();
        var allRows = sheet.getDataRange().getValues()
        var columnNames = allRows[0]
        var dataRows = allRows.slice(1);
        var result = { spreadsheetName: spreadsheetName, columns: columnNames, rows: dataRows };
        return result;
    } catch (error) {
        Logger.log("Error occurred: " + error.message);
        return null;
    }
}   

You'll need to modify the function that calls getAllRowsData() to pass sheetName of the sheet you want to access. For example, if you want to access the second sheet and its name is "Sheet2", you would call getAllRowsData("Sheet2").