Guidance on how to proceed with Google Form/Sheet

0
votes

I'm making the transition from VBA to Google Scripting - so struggling a bit. I would like some guidance - I hope this question is not to vauge.

I have a form that is used to collect data from 140 members of an art group. The form updates the spreadsheet and I use onFormSubmit (in the spreadsheet) to send the user a response enmail showing what they have submitted. I'm not using the built in email collection/response facility - I'm doing it manually. Not yet deployed but working well in testing.

Occasionally, a user will make a mistake and the data will need fixing.

What is the best way of handling this given many of the users do not have Google accounts - so no login.

Many thanks, Roy

1
google-apps-scriptgoogle-sheetsgoogle-forms
The question is primarily opinion based as it doesn't describe the criteria to qualify something as "the best way", by the other hand the title is not specific and the body doesn't include a brief description of the search/research efforts in contrary as is suggested on How to Ask. - Rubén
Oh dear - apologies if I have got this wrong. I have Googled and tutorialed myself to death on this one. Trouble is I'm here on my own with no one to brainstorm with. It's difficult to be specific when one is looking for guidance on possible ways to proceed. "I was hoping for someone to come back with something like - "the normal way to handle a situation like this is to... look at di da dia functions... ..." Sort of point me in the right direction. Anyway, apologies if I have wasted peoples time. Roy - RZB

1 Answers

0
votes

I have a google sheet that I have a Custom HTML Form that submits to my spreadsheet. You might be able to use it and rework it for your needs.

google-script

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('form4.html');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showModalDialog(html, 'Pop up Form');
}

function doGet(e){
    return HtmlService.createHtmlOutputFromFile('index').setTitle('Adding Rows');
}

function doPost(e) {
    Logger.log(e);
}

function sendText(data){
      var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow([data.item_number, data.shop_number, data.escalation, data.Hide1, data.notes, data.problem, data.added_by, data.incoive_date, data.location_order, data.user_working, data.CS_rep, data.shipping, data.department]);

    return 'success!';
}

HTML

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
    </head>
    <style>
    input[type=text], select {
        width: 100%;
        padding: 12px 20px;
        margin: 8px 0;
        display: inline-block;
        border: 1px solid #ccc;
        border-radius: 4px;
        box-sizing: border-box;
    }

    input[type=submit] {
        width: 100%;
        background-color: #4CAF50;
        color: white;
        padding: 14px 20px;
        margin: 8px 0;
        border: none;
        border-radius: 4px;
        cursor: pointer;
    }

    input[type=submit]:hover {
        background-color: #45a049;
    }

    div {
        border-radius: 5px;
        background-color: #f2f2f2;
        padding: 20px;
    }
    #hide  {
    display: none;
    }
    </style>
    <body>
        <form onsubmit="sendText(event)" id="form1" >
            <div>
                <label>Item Number</label>
                <input type="text" name="item_number" placeholder="What is the item Number ie. FBY12345"/>

                <label>Shop Number</label>
                <input type="text" name="shop_number" placeholder="Shop Order Number"/>

                <label>Escalation</label>
                <select name="escalation" >
                <option value="No Escalation ">No Escalation </option>
                <option value="72 Hour Escalation ">72 Hour Escalation </option>
                <option value="48 Hour Escalation">48 Hour Escalation</option>
                <option value="24 Hour Escalation ">24 Hour Escalation </option>
                </select>

                <label id="hide">Hide1</label>
                <input type="text" name="Hide1" id="hide" />


                    <label>Notes</label>
                <input type="text" name="notes" placeholder="Notes"/>

                        <label>What is the Problem - Select the best option</label>
                <select name="problem">
                <option value="issue 1">issue 1</option>
                <option value="issue 2">issue</option>
                <option value="issue 3">issue</option>
                <option value="issue 4">issue</option>
                <option value="issue 5">issue</option>
                <option value="issue 6">issue</option>
                <option value="issue 7">issue</option>
                <option value="issue 8">issue</option>
                <option value="issue 9">issue</option>
                <option value="issue 10">issue</option>

              </select>

                <label>Your Name</label>
              <select name="added_by">
                <option value="your name">Find Your Name</option>
                <option value="name1">name1</option>
                <option value="name2">name2</option>
                <option value="name3">name3</option>
                <option value="name4">name4</option>
                <option value="name5">name5</option>
                <option value="name6">name6</option>
                <option value="name7">name7</option>
                <option value="name8">name8</option>
                <option value="name9">name9</option>
                <option value="name10">name10</option>

                </select>

              <label>Date on Invoice</label> <br />
                <input type="date" name="incoive_date" placeholder="dd/mm/yyyy"/>


                   <br /> <br />             
                <label>Location of order </label>
                <input type="text" name="location_order" placeholder="FBY Bin 20"/>

                <label>Assign to</label>
                <select name="user_working">
                <option value="name1">name1</option>
                <option value="name2">name2</option>
                <option value="name3">name3</option>
                <option value="name4">name4</option>
                <option value="name5">name5</option>
                <option value="name6">name6</option>
                <option value="name7">name7</option>
                <option value="name8">name8</option>
                <option value="name9">name9</option>
                <option value="name10">name10</option>

                </select>

                        <label >Customer Service Rep on account</label>
              <select name="CS_rep">
                <option value="name1">name1</option>
                <option value="name2">name2</option>
                <option value="name3">name3</option>
                <option value="name4">name4</option>
                <option value="name5">name5</option>
                <option value="name6">name6</option>
                <option value="name7">name7</option>
                <option value="name8">name8</option>
                <option value="name9">name9</option>
                <option value="name10">name10</option>

                </select>

                <label>Shipping Request</label>
              <select name="shipping">
                <option value="None">None</option>
                <option value="UPS">UPS</option>
                <option value="USPS">USPS</option>
                <option value="Asendia">Asendia</option>
            </select>
                <label>Department</label>
                <select name="department">
                <option value="Your Sheet 1">Your Sheet 1</option>
                <option value="Your Sheet 2">Your Sheet 2</option>
                <option value="Your Sheet 3">Your Sheet 3</option>
                <option value="Your Sheet 4">Your Sheet 4</option>
                <option value="Your Sheet 5">Your Sheet 5</option>
                <option value="Your Sheet 6">Your Sheet 6</option>
                <option value="Your Sheet 7">Your Sheet 7</option>

                </select>
            </div>
            <div>
                <input type="submit" id="submit-form" value="Submit">

            </div>
        </form>
        <script>
            function sendText(e) {
            e.preventDefault();

            var data = {
item_number: e.target['item_number'].value,
                shop_number: e.target['shop_number'].value,
                escalation: e.target['escalation'].value,
                Hide1: e.target['Hide1'].value,
                notes: e.target['notes'].value,     
                problem: e.target['problem'].value,     
                added_by: e.target['added_by'].value,       
                incoive_date: e.target['incoive_date'].value,
                location_order: e.target['location_order'].value,
                user_working: e.target['user_working'].value,
                CS_rep: e.target['CS_rep'].value,       
                shipping: e.target['shipping'].value,       
                department: e.target['department'].value
            }

            google.script.run.withSuccessHandler(function(response) {
            console.log(response);
            google.script.host.close()

            })
            .sendText(data);
            }
        </script>
    </body>
</html>

You will need to make sure your first row on your sheet matches with the name tag(name="department") in your HTML and Javascript.

Here is my test form so you can see how it works https://docs.google.com/spreadsheets/d/1iWQ40boplJcJmdFg9HNIyOAOrHOjlCRu362LWRdV5y0/edit?usp=sharing

Hope this helps