Automatically fill out form fields by selecting data from a spreadsheet

I successfully created a web application using Google Apps Script (GAS) that sends data on submission to Spreadsheet A. Furthermore, I have implemented a select option that dynamically fetches data from another spreadsheet B ("xxx") in column A.

Below is the code snippet:

In Code.gs:

function getAddressOptions() {
  var sheet = SpreadsheetApp.openById("xxx").getSheetByName("Sheet3");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A1:A" + lastRow); 
  var data = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += '<option>' + data[i][0] + '</option>';
  };
  return optionsHTML;
}

In Index.html:

<select class="custom-select" name="test" id="test">
 <?= getAddressOptions(); ?>
</select>

I intend to auto-fill two other fields in the form based on Columns B and C corresponding to the value in Col A selected in the dropdown.

For example, if "Italy" is chosen in the select option from spreadsheet B ("xxx"), there would be two readonly fields with the values "Tom" and "Red."

+-----------------------+---------+-------+
| Col A (Select Option) |  Col B  | Col C |
+-----------------------+---------+-------+
| Italy                 | Tom     | Red   |
| USA                   | Michael | Green |
| Africa                | Anna    | Blue  |
+-----------------------+---------+-------+

(Output)

https://i.sstatic.net/dNL5p.jpg

What steps should I follow next?

Update 1 (Attempting solution provided by Tanaike)

Code.gs

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index')
      .evaluate();
}

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

/* @Process Form */
function processForm(formObject) {
  var url = "xxxx";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  ws.appendRow([formObject.company,
                formObject.test,
                formObject.field1,
                formObject.field2]);
}

function getAddressOptions() {
  var sheet = SpreadsheetApp.openById("xxxx").getSheetByName("Sheet3");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:C" + lastRow); 
  var data = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += `<option data-values="${data[i][1]},${data[i][2]}">${data[i][0]}</option>`; 
  };
  return optionsHTML;
}

Index.html

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    <body>
        <div class="container">
            <div class="row justify-content-center">
                <div class="col-8">
                    <form id="myForm" onsubmit="handleFormSubmit(this)">
                        <p class="h4 mb-4 text-center">Contact Details</p>

                        <div class="form-row">
                            <div class="form-group col-md-12">
                                <label for="company">Company</label>
                                <input type="text" class="form-control" id="company" name="company" required>
                            </div>
                            
                        <div class="form-group col-md-2">
                             <label for="test">Test field 1</label>
                             <select class="custom-select" name="test" id="test">
                             <?!= getAddressOptions(); ?>
                             </select>
                        </div>
                        
                        <div class="form-group col-md-5">
                            <label for="field1">Field 1</label>
                            <input type="text" class="form-control" id="field1" name="field1" disabled>
                        </div>
                        
                        <div class="form-group col-md-5">
                            <label for="field2">Field 2</label>
                            <input type="text" class="form-control" id="field2" name="field2" disabled>
                        </div>
                        
                    </div>

                        <button type="submit" class="btn btn-primary btn-block">Insert in Master Leads</button>
                    </form>
                    <div id="output"></div>
                </div>
            </div>      
        </div>
    </body>
  <?!= include('JavaScript'); ?>
</html>

Javascript.html

<script>
  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);    
      
      
  function handleFormSubmit(formObject) {
    google.script.run.processForm(formObject);
    document.getElementById("myForm").reset();
  }
  
  function setValues(select) {
    const [value1, value2] = select.options[select.selectedIndex].dataset.values.split(",");
    document.getElementById("field1").value = value1;
    document.getElementById("field2").value = value2;
  }

  const select = document.getElementById("test");
  setValues(select);
  select.addEventListener("change", () => setValues(select));

</script>
javascriptgoogle-apps-scriptgoogle-sheetsbootstrap-4web-applications

Answer №1

It appears that your objective is as follows.

  • When a selection is made for the option in column "A", you wish to populate the values from columns "B" and "C" into input tags set as readonly.

Key Modifications:

  • In this update, executing address() at <?!= address(); ?> assigns the values of columns "A" through "C" to the options. The values of columns "B" and "C" are then allocated to a custom attribute. Additionally, 2 new input tags with the disabled="disabled" attribute have been included in the HTML section as needed.
  • If another option is chosen, the values from columns "B" and "C" will be assigned to 2 input tags using Javascript.
  • Based on the provided sample data in the Spreadsheet, it seems that the first row serves as the header row.

After implementing the script modifications, the transformation is reflected below.

Revised Script:

Google Apps Script Integration: Code.gs

function address() {
  var sheet   =  SpreadsheetApp.openById("xxx").getSheetByName("Sheet3");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:C" + lastRow);  // Updated
  var data    = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += `<option data-values="${data[i][1]},${data[i][2]}">${data[i][0]}</option>`;  // Updated
  };
  return optionsHTML;
}

HTML & Javascript Implementation: index.html

This adaptation introduces 2 additional input tags along with Javascript functionality.

<select class="custom-select" name="test" id="test">
  <?!= address(); ?>
</select>
<input type="text" id="field1" disabled="disabled">
<input type="text" id="field2" disabled="disabled">

<script>
function setValues(select) {
  const [v1, v2] = select.options[select.selectedIndex].dataset.values.split(",");
  document.getElementById("field1").value = v1;
  document.getElementById("field2").value = v2;
}

const select = document.getElementById("test");
setValues(select);
select.addEventListener("change", () => setValues(select));
</script>

Additional Note:

  • To maximize compatibility, ensure that Google Apps Script is operated with V8 enabled.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Error: The function coolArr.printArray is not defined in the npm package being imported

My custom package contains an index.js file with the following code: module.exports = class CoolArray extends Array { printArray() { console.log(this); } } After transpiling the code using es2015 syntax with Babel, I connected the package to my te ...

javascriptnode.jsnpmbabeljses6-class

What steps do I need to take in order to implement a functional pagination menu in Vue?

I downloaded and installed laravel-vue-pagination with the following command: npm install laravel-vue-pagination After that, I globally registered it in my app.js file: Vue.component('pagination', require('laravel-vue-pagination')); F ...

javascriptajaxlaravelvue.js

Optimizing Google e2e testing using Protractor

Improving login efficiency is necessary to enhance the speed of executing e2e tests. At present, after every test, the Chrome browser shuts down, requiring a new login session for each subsequent test. What changes can be made to address this issue? Any ...

javascriptseleniumselenium-webdriverprotractore2e-testing

Obtain the text content of a div using JavaScript

Hello, I am new to Javascript! I have a table structure that looks like this: <table id='master_tbl'> <tbody> <tr id="master_hr'> <td class="myclass"> <table> <tbody ...

javascripthtml

It is not possible to delete a class from an element once it has been added

Issue can be replicated by visiting the following URL: Click on the hamburger icon to open the navigation menu Click on "Services" Click "< Services" within the submenu to attempt to go back For some reason, the removeClass method is not removing t ...

javascriptjquerycss

How to handle multiple formData input in NestJS controller

How can I create a controller in Nest.js that accepts two form-data inputs? Here is my Angular service code: public importSchema(file: File, importConfig: PreviewImportConfig): Observable<HttpEvent<SchemaParseResponse>> { const formData = ...

javascriptangulartypescriptnestjs

From transforming nested JSON into flat JSON structures within Javascript

Currently, I am faced with a challenge in my JavaScript programming. The task at hand is to transform the JSON data obtained from an API into a different structure while maintaining the essence of the original JSON. The existing JSON comprises members, num ...

javascriptjsonnested

What prevents me from extending an Express Request Type?

My current code looks like this: import { Request, Response, NextFunction } from 'express'; interface IUserRequest extends Request { user: User; } async use(req: IUserRequest, res: Response, next: NextFunction) { const apiKey: string = ...

javascripttypescriptexpress

The model.find operation is failing to retrieve the necessary fields from the database

When I execute console.log(correct.password), it returns undefined, even though the if condition results in false. app.post('/login' , async (req , res)=> { const correct = data.findOne({name : req.body.name}).select({name : 0}); if(!c ...

javascriptnode.jsmongodbmongoosefind

Ways to show alternative data from a database in Laravel 8

I am working on a project where I need to display additional data based on the option selected from a dropdown menu populated from a database. Can anyone guide me on how to achieve this using javascript or jquery? https://i.stack.imgur.com/k3WLl.png Belo ...

javascriptphphtmljquerylaravel

The use of ReactDom.render is no longer permissible in Next.js

I just set up a fresh Next JS application using Next 12. Encountering this issue consistently on every page load in the browser: Alert: The use of ReactDOM.render is no longer supported in React 18. Please switch to createRoot instead. Until you make th ...

javascriptreactjsnext.js

Is it possible to access prop properties within the ready() function?

I am seeing the error message undefined in the console, specifically originating from the ready() function. The issue I am encountering is related to attempting to assign the value of this.users.name to this.userForm.name. Can someone please point out wh ...

javascriptvue.js

How can I preserve the line break in a textarea using PHP?

Is it possible to maintain line breaks in a textarea using PHP? Currently, I have a temporary solution that involves using the exec function to run a shell command, but I would prefer a purely PHP approach. Below is my temporary script - can you help me mo ...

javascriptphp

Incorporate a dynamic fading effect for text and images using JQuery

I successfully implemented a Crossfade effect using Jquery: function doAnimationLoop(o, n, t, i, a) { fadeInOut(o, n, t, i, function() { setTimeout(function() { doAnimationLoop(o, n, t, i, a) }, a) ...

javascriptjqueryhtmlcss

Error occurred due to an unexpected end of JSON input following a pending promise

I am currently developing a data handler that requires downloading a file for parsing and processing within the handler. To handle this, I have implemented the file request within a promise and called it asynchronously from other methods. Including the h ...

javascriptnode.jspromise

Mongoose Exception: Question does not have a constructor

After coming across numerous questions on stack overflow related to this error without finding a solution that works for me, I've decided to ask my own question. (probably due to my limited understanding of javascript/node/mongoose) The modules I am ...

javascriptnode.jsmongodbexpressmongoose

Implementing a dynamic text field feature with JavaScript and PHP

Field Item                 Field Qty --------                 ----- --------                 ------ --------       ...

phpjavascriptdynamic

Is there a way to detect a specific button press in react-native-picker-select?

I am currently utilizing the react-native-picker-select library. My objective is to set ingrebool to false when options a, b, c, or d are selected and true when option e is chosen from the data called ingre. How can I achieve this? Here is my code snippet ...

javascriptreactjsreact-native

Is there a way to modify an npm command script while it is running?

Within my package.json file, I currently have the following script: "scripts": { "test": "react-scripts test --watchAll=false" }, I am looking to modify this script command dynamically so it becomes: "test&qu ...

javascriptnpmnpm-scripts

If the user confirms it with a bootstrap dialog box, they will be redirected to the corresponding links

Firstly, I am not interested in using javascript confirm for this purpose. Please read on. For example, adding an onClick attribute to each link with a function that triggers a system dialog box is not what I want. Instead of the standard system dialog bo ...

javascripthtmlcsstwitter-bootstraphttp-redirect