View specific row based on username in Google Shee

问题:

Let's say I have a google site. On the site is a gadget to a spreadsheet. I want the gadget to show one specific row from the spreadsheet based on the username of the person viewing the site. Is this possible with GAS?

Update:

function onOpen() {
    var ss = SpreadsheetApp.openById("abcdefg123456789");
    var first = ss.getSheetByName("first");
    var maxRows = first.getMaxRows();
    var email = Session.getActiveUser().getEmail();

    //show all the rows
    first.showRows(1, maxRows);

    var data = first.getRange('A:A').getValues();

    //iterate over all rows
    for (var i = 0; i < data.length; i++) {
        if (data[i][0] !== email) {
            first.hideRows(i + 1);
        }
        if (data[i][0] === "") {
            return;
        }
    }
}

The issue with this code, or maybe it's an issue with GAS, is I can't get it to run on sites. I linked this code to a spreadsheet and it does what it's supposed to when I open it (hides every row that doesn't have my email address). However, if I link the same spreadsheet to a google site, the code does not seem to run and the windows will still show all the other rows.

回答1:

You can use a web app proxy between your spreadsheet and the gadget. This technique is demonstrated (along with working code to do what you're looking for) in Romain's Row-level permissions addendum to his Awesome Tables gadget.

回答2:

I don't think so, you would need to do the script 'on open' and the gadget does not fire that trigger.

You could create an apps script for the site that 1. gets the current active user 2. looks up the data on the spreadsheet 3. returns html for the site page

publish the script as a webapp, execute as user running app

include apps script gadget where you want the html