google-sheetsgoogle-apps-scriptweb-applications

Using a gs function with an input from a selection in html on appscript

I'm trying to use a gs function using an input provided by an html. This input is chose from creating a list with the gs function listWebinarSheets_i which works. I've tried many things, nothing works, when I console.log(google.script.run), I can see my function but it is not getting executed. I've simplified the function called to just console.log("1"); but still nothing on the console log.

My html :

<!DOCTYPE html>
<html>    
    <head>
    <base target="_top">
    <script>
        function copyParticipants() {
        const selectedSheet = document.getElementById('sheetSelect').value;  
        google.script.run<!DOCTYPE html>
<html>
    <head>
    <base target="_top">
    <script>
    function copyParticipants() {
        const selectedSheet = document.getElementById('sheetSelect').value;  
        google.script.run
            .withSuccessHandler(function() {
                alert('won : ' + selectedSheet);
            })
            .withFailureHandler(function(error) {
                alert('Erreur : ' + error.message);
            })
            .copytoto(selectedSheet);  
        }
    </script>
    </head>
    <body>
        <h3>Select a sheet :</h3>
    
    <!-- List -->
    <select id="sheetSelect">
        <? for (var i = 0; i < sheetNames.length; i++) { ?>
            <option value="<?= sheetNames[i] ?>"><?= sheetNames[i] ?></option>
        <? } ?>
    </select>
    
    <br><br>

    <button onclick="copyParticipants()">Copy Participants</button>


    </body>
</html>

my GS :

function showInterface_i() {
    var sheetNames = listWebinarSheets_i();  // getting a list of names
    var template = HtmlService.createTemplateFromFile('interface');
    template.sheetNames = sheetNames;  
  
    const html = template.evaluate()
        .setWidth(400)
        .setHeight(300);
    SpreadsheetApp.getUi().showModalDialog(html, 'Interface');
}


function copytoto(sheetname){
    console.log("1");
}

I've tried to put console log everywhere, on the html it's working as intended, but on the function called with google.script.run (so in the .gs), none. Other than console logs, the initial function works well alone. I've tried to come up with a solution from templated html but nothing is working yet.

Solution

  • Failing to Execute using Google.script.run

    I have run some test and figure out that you might be missing things to pair with your SuccessHandlers and Failure Handles, since if stripped down to basics of running things I was able to run the Copy function. I added some code to pair with the error handlers and success Handlers. I also added a few parts so I can replicate the case feel free to edit them as you see fit. Corrected some misplaced tags on the HTML.

    Sample Code

    HTML:

    <!DOCTYPE html>
<html>
<head>
    <base target="_top">
    <script>
        function copyTo() {
            const selectedSheet = document.getElementById('sheetSelect').value;  
            google.script.run
                .withSuccessHandler(function(message) {
                    alert('won : ' + message); 
                })
                .withFailureHandler(function(error) {
                    alert('Erreur : ' + error.message);
                })
                .copyData(selectedSheet); 
        }
    </script>
</head>
<body>
    <h3>Select a sheet:</h3>
    
    <!-- List -->
    <select id="sheetSelect">
        <? for (var i = 0; i < sheetNames.length; i++) { ?>
            <option value="<?= sheetNames[i] ?>"><?= sheetNames[i] ?></option>
        <? } ?>
    </select>
    
    <br><br>

    <button onclick="copyTo()">Copy Participants</button>
</body>
</html>

    Code.gs

    function showInterface_i() {
    var sheetNames = listWebinarSheets_i();  // Get a list of names
    var template = HtmlService.createTemplateFromFile('interface');
    template.sheetNames = sheetNames;  
  
    const html = template.evaluate()
        .setWidth(400)
        .setHeight(300);
    SpreadsheetApp.getUi().showModalDialog(html, 'Interface');
}

function copyData(sheetName) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    if (!sheet) {
        throw new Error('Source sheet not found: ' + sheetName);
    }

    var data = sheet.getDataRange();
    var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TargetSheet");
    if (!targetSheet) {
        throw new Error('Target sheet not found: TargetSheet'); // Throwing an error for something to handle your onFailure
    }

    data.copyTo(targetSheet.getRange(1, 1), {contentsOnly: true});
    return 'Data copied from: ' + sheetName; // Returning a message on Success to match your HTML Google Script Run Requirements
}

function listWebinarSheets_i() { //Created a sudo list generation since it is not included on your post.
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    return sheets.map(x => x.getSheetName()); 
}

    Sample Output

    Sample Output with Success

    References:

    Google Script Run

    Note:

    The edited code is kind of slow, please be patient in testing them. Feel free to ask questions about the code. Please make sure that Sheet Names on the Copy part of the script must be modified to match your current project