Google Sheets HTML Service Sidebar to run code as owner

Question

So my goal is to create a HTML Service Sidebar that users can use to create entries on a protected sheet that only the owner has access to edit. I can't seem to figure out a way to do this and I'm going to cover all the things I've tried below.

Here is the code that will be in any solution:

Sidebar.html

<link rel="stylesheet" href="//ssl.gstatic.com/docs/script/css/add-ons.css">

<div class="sidebar branding-below">
    <form id="submission">
        <div class="block">
            <div class="inline form-group">
                <label for="date">Date</label>
                <input id="date" name="date" type="text" value="" />
            </div>
        </div>

        <div class="block">
            <div class="inline form-group">
                <label for="name">Name</label>
                <input id="name" name="name" type="text" value="" />
            </div>

            <div class="inline form-group">
                <label for="number">Number</label>
                <input id="number" name="number" type="text" value="" />
            </div>
        </div>

        <div class="block">
            <button id="submit" type="submit" class="action">Submit</button>
        </div>
    </form>
</div>

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>

Sidebar Javascript.html

<script>
    $(document).ready(function() {
        $("#submit").click(function(e) {
            google.script.run
                .submitEntry(document.forms[0]);

        // prevents app from trying to serve new page after submission
        e.preventDefault();
    });
</script>

Server-side code that doesn't change:

function showSidebar() {
    var ui = HtmlService.createTemplateFromFile("Sidebar")
        .evaluate()
        .setSandboxMode(HtmlService.SandboxMode.IFRAME)
        .setTitle("Data Entry");
}

function include(filename) {
    return HtmlService.createHtmlOutputFromFile(filename)
        .setSandboxMode(HtmlService.SandboxMode.IFRAME)
        .getContent();
}

The rest of the server-side code is where I've tried so many different things to get this to work. The major problems are the onEdit function not getting called at all (my theory being because the edit was made programmatically) and permissions issues.

Methods I've tried:

1. submitEntry adds a row to the sheet and calls a method to do some calculations on the sheet. In this case, I get an error about the user not having authorization to make edits.
2. submitEntry adds a row to the sheet (using the appendRow method) with an installable onEdit trigger. In this case, the onEdit method is never called when the edit is made from the submitEntry function, but works fine when the same user manually edits one of the cells. I thought this would work because the onEdit function is supposed to run as the owner, but it never gets called when the edit is made programmatically.
3. Create a form that links to the spreadsheet. Have an onFormSubmit installable trigger that gets the new form values, appends values to the sheet I am interested in and then runs the calculations. This works for the form submit, but I want the form to be from the sidebar, so I simulated the form submit from the sidebar using the method explain in this StackOverflow page, but this does not seem to simulate a form submit properly (no data received on spreadsheet). I get the error "Request failed for returned code 500." when running as myself and an error message "Authorization is required to perform that action" when logged in as someone else.
4. Same as 3 except I post the content from Javascript using Ajax. The result is a "No 'Access-Control-Allow-Origin' header is present on the requested resource." error message. I tried altering the origin and referer in the request by changing the header properties, but it looks like those are overwritten before data is actually posted. There are also hidden input fields where their values fluctuate on every reload of the form.

I've tried many other methods but found reasons why they wouldn't work through the documentation.

No matter the method I try, none of the code ever runs for anonymous users, which is also a problem.

Does anyone have any ideas on how I can get this to work? I feel like it's a simple problem but just can't seem to figure it out.

Example of my attempt at method 2 described above:

// should run as user who submitted form on sidebar
function submitEntry(form) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    sheet.appendRow([form.date, form.name, form.number]);
}

// should run as user who installed script
// never gets called????
function onEditInstallable(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var data = sheet.getDataRange().getValues();
    sheet.clear();

    sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    sheet.appendRow([data[0][0], data[0][1], data[0][2]]);

    doCalculationStuff();
}

Answer 1

This doesnt work, the user can only do what their document access dictates. The sidebar is run with the same rights: A user without write access can not post to a document through sidebar scripts.

You need to use google forms to collate data and have it post that to a document the user never gets to see.

Answer 2

See Google sheets, running a function as owner

You can only run a script as owner ONLY if it's installed trigger. There are only a few trigger events you can use: onOpen, onEdit, onInstall, doGet, and doPost. Of these, only onEdit will work. So you cannot run the interface from menu, dialog, or sidebar. You can only run the interface only if it's part of the spreadsheet.

---

1. First, put all your user interface directly on the spread sheet, and have a submit cell with drop down menu of { "submit" } (via validation rule)
2. Create a function, say apply(), which checks if "Submit cell" is the active cell. If yes, apply all field values to the spreadsheet, then clear all the input fields.
3. In your project script editor, go to Resources > Current project's triggers
4. Create the following rule: run apply from spreadsheet on edit
5. Since apply() is an installed trigger, it will run with the owner permission.