How to append values ​​to google sheet when submitting a form using developers.google.com/sheets/api/?

Question

JavaScript

A little man - he's also a user - comes to the site, enters information into the right form? and this information should be recorded in a google sheet (table / sheet). This must be done on behalf of one person, say, me. Without any action on the part of the client entering the site.

What does the Google Sheets API offer? Update this very table on behalf of this user, that is, a pop-up window appears that requests permission to enter this project, and, in principle, checks (seemingly) whether the user is authorized.

Most likely, I did not fully understand the API, or overlooked something.

What am I doing wrong? Help solve this puzzle!

My code basically matches the example code from the Google Sheets API site, with the exception of buttons and authorization checks:

<script type="text/javascript">
    // Client ID and API key from the Developer Console
    let CLIENT_ID = 'CLIENT_ID';
    let API_KEY = 'API_KEY';

    // Array of API discovery doc URLs for APIs used by the quickstart
    let DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];

    // Authorization scopes required by the API; multiple scopes can be
    // included, separated by spaces.
    let SCOPES = "https://www.googleapis.com/auth/spreadsheets";

    /**
     *  On load, called to load the auth2 library and API client library.
     */
    function handleClientLoad() {
        gapi.load('client:auth2', initClient);
    }

    /**
     *  Initializes the API client library and sets up sign-in state
     *  listeners.
     */
    function initClient() {
        gapi.client.init({
            apiKey: API_KEY,
            clientId: CLIENT_ID,
            discoveryDocs: DISCOVERY_DOCS,
            scope: SCOPES
        }).then(function () {
            console.log('Connect!');
        }, function(error) {
            console.log(JSON.stringify(error, null, 2));
        });
    }

    function handleAppendButton(data) {
        let params = {
            spreadsheetId: 'spreadsheetId',  // TODO: Update placeholder value.
            range: 'Data!A:C',  // TODO: Update placeholder value.
            valueInputOption: 'USER_ENTERED',  // TODO: Update placeholder value.
        };

        let valueRangeBody = {
            values: [data]
        };

        let request = gapi.client.sheets.spreadsheets.values.append(params, valueRangeBody);

        request.then(function(response) {
            console.log(response);
        }, function(reason) {
            console.log('error: ' + reason.result.error.message);
        });
    }

    /**
     *  Called when the signed in status changes, to update the UI
     *  appropriately. After a sign-in, the API is called.
     */
    function updateSigninStatus(isSignedIn) {
        if (!isSignedIn) {
            gapi.auth2.getAuthInstance().signIn();
        }
    }

    window.onload = function () {
        $('#form_submit').on('click', (e) => {
            e.preventDefault();
            let name = $('input[name=name]').val();
            let tel = $('input[name=tel]').val();
            let product = $('input[name=product]').val();

            $.ajax({
                url: `/thank-you.php?name=${name}&tel=${tel}&product=${product}`,
                method: 'GET',
                dataType: 'json',
                success: function(json) {
                    handleAppendButton([name, tel, product]);
                }
            })
        });
    }
</script>
<script async defer src="https://apis.google.com/js/api.js"
        onload="this.onload=function(){};handleClientLoad()"
        onreadystatechange="if (this.readyState === 'complete') this.onload()">
</script>

Answer 1

To take advantage of this feature, for security reasons, you need to use an intermediate server that will authorize for you. It can be a minimal cloud server, or a cloud function, or a published web application on Google Apps Script.