Tag Archive

How to use Google Apps Script to get data from a connected data sheet


Posted on May 13, 2023 by alanysiu

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]]);
        }
    }


0

Changing ownership of files in Google Drive via Google Apps Script


Posted on March 20, 2020 by alanysiu

This was a fun little exercise in Google Apps Script to essentially transfer ownership of Google Drive files from one user to another.

Here’s a script that the old user should run to change ownership of those files to the new user:
ChangeFileFolderOwnership.js

And here’s a script that the new user user should run to remove the old user as editor from the newly changed-ownership files:
RemoveEditorFromDriveFiles.js

Note: You cannot use the file.removeEditor() command to remove yourself as an editor, so that actually has to be run from the new owner’s account.

Another Note: Just as when you change ownership or add editorship manually, scripting will also produce a ton of emails to the new editor/owner that you’ve add that person, so the new editor/owner may want to set up some temporary Gmail filters to mark those as read and put into the trash.


12