'Can't Pass WebApp Html ElementID Text to Googl Sheet

I've tried searching for a similar problem on here, and the closet I found was converting object to strings using Stringify and back again using Parse. (Google html service to sheets)

However, I am completely new to JSON and with what i'm trying to do, so I was wondering IF indeed the above will help me or not. What I need is simple.

I have the below code for Google Sheets Script

//https://www.youtube.com/watch?v=g7HFp0e7AnU'
//https://codewithcurt.com/create-file-loader-google-web-app-to-google-drive/
//https://gist.github.com/tanaikech/2f16f467c94612dc83920a3158614d95

var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var message = "";

function onOpen(){
  checkFolderExists();
}

function doGet(e) {
  var htmlOutput = HtmlService.createTemplateFromFile('UploadFile');
  htmlOutput.message = '';
  return htmlOutput.evaluate();
}

function doPost(e) {
  Logger.log(JSON.stringify(e));

  var receiptFolder = DriveApp.getFoldersByName("Expense Receipts").next().getId();
  var destination_id = receiptFolder;  // ID OF GOOGLE DRIVE DIRECTORY;
  var destination = DriveApp.getFolderById(destination_id);

  var data = Utilities.base64Decode(e.parameter.fileData);
  var blob = Utilities.newBlob(data, e.parameter.mimeType, e.parameter.fileName);
  destination.createFile(blob);
  var url = destination.getFilesByName(e.parameter.fileName).next().getUrl();


  listRecord(e.parameter.description, e.parameter.paymentCard, e.parameter.receiptAmount, url);

  var htmlOutput = HtmlService.createTemplateFromFile('UploadFile');
  htmlOutput.message = 'File Uploaded';
  return htmlOutput.evaluate();
}

function listRecord(description, paymentCard, receiptAmount, fileURL) {
  var url = spreadsheet.getUrl();  //URL OF GOOGLE SHEET;
  var ss = SpreadsheetApp.openByUrl(url);
  var expensesSheet = ss.getSheetByName("Expense Receipts");
  var currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd MMM yyyy")

  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getActiveSheet();
  var localURL = '';
  localURL += SS.getUrl();
  localURL += '#gid=';
  localURL += ss.getSheetId();

  expensesSheet.appendRow([currentDate, description, paymentCard, receiptAmount, '=hyperlink("' + fileURL + '","View Receipt")']);
}

function getUrl() {
  var url = ScriptApp.getService().getUrl();
  return url;
}

function showDialog() {
  var htmlTemplate = HtmlService.createTemplateFromFile('UploadFile').evaluate();
  var htmlOutput = htmlTemplate.setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setTitle('Upload Expense Receipt')
    .setWidth(500)
    .setHeight(500);

  SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Upload Expense Receipt");
}


function checkFolderExists() {
  var fileID = DriveApp.getFileById(spreadsheet.getId());
  var currentFolderID = fileID.getParents().next().getId();
  createFolder(currentFolderID, "Expense Receipts");
};

function createFolder(folderID, folderName) {
  var parentFolder = DriveApp.getFolderById(folderID);
  var subFolders = parentFolder.getFolders();
  var doesntExist = true;
  var newFolder = '';
  // Check if folder already exists.
  while (subFolders.hasNext()) {
    var folder = subFolders.next();
    //If the name exists return the id of the folder
    if (folder.getName() === folderName) {
      doesntExists = false;
      newFolder = folder;
      return newFolder.getId();
    };
  };
  //If the name doesn't exists, then create a new folder
  if (doesntExists = true) {
    //If the file doesn't exists
    newFolder = parentFolder.createFolder(folderName);
    return newFolder.getId();
  };
};

And the following HTML Code:

<!-- https://getbootstrap.com/docs/5.1/getting-started/introduction/
https://www.bootstraptoggle.com/
https://fontawesome.com/ -->

<!DOCTYPE html>
<html lang="en">

<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <!--Import Google Icon Font-->
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
  <!-- Compiled and minified CSS -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
  <!-- Compiled and minified JavaScript -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
  <title>Upload New Expense Receipt</title>

  <script>
    document.addEventListener('DOMContentLoaded', function() {
      var elems = document.querySelectorAll('select');
      var instances = M.FormSelect.init(elems);
    });
  </script>

  <!--Let browser know website is optimized for mobile-->
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />

  <base target="_top">
  <script>
    function LoadFile(event){
      var file = event.target.files[0];
      var reader = new FileReader();
      reader.onload = function(e) {
        console.log(e.target.result);
        var fileData = e.target.result.substr(e.target.result.indexOf(",")+1);
        var mimeTypeStart = e.target.result.indexOf("data:") + 5;
        var mimeTypeEnd = e.target.result.indexOf(";");
        var mimeType = e.target.result.substr(mimeTypeStart, mimeTypeEnd - mimeTypeStart);

        //Format today's date for use generating filename
        var today = new Date();
        var dd = String(today.getDate()).padStart(2, '0');
        var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
        var yyyy = today.getFullYear();
        today = yyyy + '-' + mm + '-' + dd;

        var fileName = today + ' ' + document.getElementById("description").value;
        document.getElementById("fileName").value = fileName;
        // var fileName = file.name;
        document.getElementById("fileData").value = fileData;
        document.getElementById("mimeType").value = mimeType;
  
        var elemPaymentCard = document.getElementById("paymentCard");
        var paymentCard = elemPaymentCard.options[elemPaymentCard.selectedIndex].text;
        var receiptAmount = document.getElementById("amount").value;
      };
      reader.readAsDataURL(file);
    }
  </script>
</head>

<body>
  <h4>Upload New Expense Receipt</h4>
  <?var url = getUrl();?>
  <div class="row">
    <form class="col s12" method="post" action="<?= url ?>">
      <div class="row">
        <div class="input-field col s6">
          <input placeholder="What was purchased" id="description" name="description" type="text" class="validate">
          <label for="description">Description</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s12 m6">
          <select id="paymentCard" name="paymentCard" class="icons">
            <option value="" disabled selected>Choose your option</option>
            <optgroup label="Business Cards">
              <option value="1" data-icon="https://drive.google.com/uc?id=14MCi1Uu5DXZEIBPFYiFToMOKoy2HlXj5">Business Debit Card Ending 1111</option>
            </optgroup>
            <optgroup label="Personal Cards">
              <option value="2" data-icon="https://drive.google.com/uc?id=1elCGqEL7FeZIaUUK1bM82T78-EaC1slU">Personal Credit Card Ending 2222</option>
            </optgroup>
          </select>
          <label>Payment Method</label>
        </div>
      </div>

      <div class="row">
        <div class="input-field col s6">
          <input placeholder="Enter receipt amount" id="amount" name="amount" type="text" class="validate">
          <label for="amount">Total Amount</label>
        </div>
      </div>

      <div class="row">
        <div class="file-field input-field">
          <div class="btn">
            <span>Select File</span>
            <input type="file" name="file" onchange="LoadFile(event)">
            <input type="hidden" id="fileData" name="fileData" />
            <input type="hidden" id="mimeType" name="mimeType" />
            <!-- <input type="hidden" id="fileName" name="fileName" /> -->
          </div>
          <div class="file-path-wrapper">
            <input placeholder="Select File" id="fileName" name="fileName" type="text" class="validate">
          </div>
          <!-- <div class="file-path-wrapper">
            <input class="file-path validate" type="text">
          </div> -->
        </div>
      </div>

      <div class="row">
        <button class="btn waves-effect waves-light" type="submit" name="action">Save Receipt
          <i class="material-icons right">send</i>
        </button>
      </div>

      <div class="row">
        <span><? message ?></span>
      </div>
    </form>
  </div>

  <!--JavaScript at end of body for optimized loading-->
  <script type="text/javascript" src="js/materialize.min.js"></script>
  <!-- Compiled and minified JavaScript -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
</body>

</html>

Which looks like this: Before Submit

And in Google Sheets, ends up like this:

Result

What needs to happen (that isn't), is first, the SelectedINdex's .text for the Payment Method Select Element (not the value -the displayed .text). For some reason the .value of the selected Index is passing instead. I "COULD" just put the value I want passing, into thee relevant value="", but I wish the values to remain 1 an 2, and instead be able to read the selected index's text.

Second, the Amount of purchase isn't passing.

Can anyone advise?

Is it possible to Add PaymentCard and PurchaseAmount to the list of parameters as i'm trying to do in the Script Code :

listRecord(e.parameter.description, e.parameter.paymentCard, e.parameter.receiptAmount, url);

It SHOULD have looked like this if correctly working...

Should Be this

thank you all for your patience and support.

google-apps-scriptgoogle-sheets


Solution 1:[1]

  • Question 1:

    What needs to happen (that isn't), is first, the SelectedINdex's .text for the Payment Method Select Element (not the value -the displayed .text). For some reason the .value of the selected Index is passing instead. I "COULD" just put the value I want passing, into thee relevant value="", but I wish the values to remain 1 an 2, and instead be able to read the selected index's text.

  • Question 2:

    Second, the Amount of purchase isn't passing.

Modification points:

  • In the case of question 1, how about preparing an object for converting the index to the value? Because, in your script, the display value is not included in the event object.
  • In the case of question 2, your HTML uses amount as the name. So in this case, it is required to modify this.

When these points are reflected in your script, how about the following modification?

From:

listRecord(e.parameter.description, e.parameter.paymentCard, e.parameter.receiptAmount, url);

To:

var obj = {"1": "Business Debit Card Ending 1111", "2": "Personal Credit Card Ending 2222"};
listRecord(e.parameter.description, obj[e.parameter.paymentCard] || "", e.parameter.amount, url);

Note:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tanaike

Related Questions

How to do I locate a Google SpreadSheet ID?

How to use REGEXEXTRACT for day and time?

How to automatically add data to another sheet when a specific cell's values changes?

How to prevent rows combining in pd.read_csv() google sheets

How to sort sheets within a spreadsheet in chronological order by date in google apps script

Get date value from header

how to combine many google sheets in one sheet by using query

Google Spreadsheet with data from WebSocket

Call a Google Sheets add-on within a Google Apps Script?

How do I access (read, write) Google Sheets spreadsheets with Python?

Custom functions intermittently hang with "Error - Loading Data"

Formula to see if you're on track of monthly budget

Get BTC prices on Google Sheets from Google Finance

Google sheets: Count Specific Characters in a string of words

How to set a sheet's number of rows and columns at creation time? (And a word about Google's "advanced parameters")