Regular sheets are straightforward

For a regular Google Sheets spreadsheet, it’s fairly easy to use Google Apps Script to get the data contained on a worksheet:

    var spreadsheet = SpreadsheetApp.getActive();
    var regularsheet = spreadsheet.getSheets()[0];
    var range = regularsheet.getDataRange();
    var values = range.getValues();
    for ( j = 0; j < values.length; j++ ){
        Logger.log(values[j][0] + ': ' + values[j][1]);
    }

The nice thing about the getDataRange() function is that it just automatically grabs all the cells have data in them. You don’t have to specify to start at this column or end at this particular row.

Connected sheets

You can connect a data source as another sheet.

Can’t treat connected sheet as regular sheet

In theory, Google provides a function called asSheet() that supposedly allows you to process a data source sheet as a regular sheet.

That doesn’t work, though.

Get values by column on connected sheet

You can get the connected sheet data by column name. That’s not optimal, but it does appear to work:

    var spreadsheet = SpreadsheetApp.getActive();
    var targetsheet = spreadsheet.getSheets()[0];
    var connectedsheet = spreadsheet.getSheets()[1].asDataSourceSheet();
    var values = connectedsheet.getSheetValues("name");
    for ( j = 0; j < values.length; j++ ){
        Logger.log(String(j) + ": " + values[j]);
        if ( values[j] == "Mary" ){
                  targetsheet.appendRow([j, values[j]]);
        }
    }