CODEWITHSUNDEEP
htmlgoogle-apps-script
Brandon Pillay
Brandon Pillay

Reputation: 1166

How to get date text input in custom HTML dialogue box to load with a value from Google Sheets?

I'm trying to change the value of a text input on a custom (HTML) dialogue box in Google Sheets to equal a value on my Google Sheet. How can I achieve this?

I'm aware of the correct format of the date that needs to be applied in order to set the .value of the text input. I can use the 'Utilities' service in the .gas.

I've tried using the onload="getDate()" event to run a function that sets the value using var date = new Date() in getDate() and not get the values from the sheet directly but that doesn't seem to work. 

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

    <style>
    label {font-family: verdana;}

    body {
    border: 1px solid powderblue;
    padding: 30px;
    }
    </style>
  </head>
  <body  onload="getDate()">

<form id="myForm">
  <label>Task Number:</label><br>
  <input type="text" name="taskNumber" value="">
  <br><br>
   <label>Task Date:</label><br>
  <input type="date" name="taskDate" value="" id="demo" >
  <br><br>
   <label>Customer Name:</label><br>
  <input type="text" name="customerName" value="">
  <br><br>
   <label>Customer Site:</label><br>
  <input type="text" name="customerSite" value="">
  <br><br>
   <label>Status:</label><br>
  <select name="status">
  <option value="NOSTATUS">Choose</option>
  <option value="ON HOLD">ON HOLD</option>
  <option value="SCHEDULED">SCHEDULED</option>
  <option value="RESCHEDULE">RESCHEDULE</option>
  <option value="WORK IN PROGRESS">WORK IN PROGRESS</option>
  </select>
  <br><br>
   <label>Status Date:</label><br>
  <input type="date" name="statusDate" value="">
  <br><br>
   <label>Location:</label><br>
  <input type="text" name="location" value="">
  <br><br>
   <label>Description:</label><br>
  <input type="text" name="description" value="">
  <br><br>
  <input type="button" style="font-family: verdana;" value="Submit" onclick="uploadTask()">

</form> 

  <script>
  function success(msg) {
    alert(msg);
  }

  function uploadTask(){
    var form = document.getElementById("myForm").elements;
    var obj ={};
    for(var i = 0 ; i < form.length ; i++){
        var item = form.item(i);
        obj[item.name] = item.value;
    }
    google.script.run
    .withSuccessHandler(success)
    .uploadTask(obj);

    google.script.host.close();
  }

  function getDate(){
  var ss = SpreadsheetApp.openById('1CtH3uNeSiJcRd_ZKE3iWp7TEtAO_B7uCNatGJ7rHMeg');
  var monthly = ss.getSheetByName('MONTHLY');         
  var date = monthly.getRange(1, 1).getValue(); //The value of A:1 = 10/12/2000 date 

  date = Utilities.formatDate(date, Session.getScriptTimeZone(), "YYYY-MM-dd")

  document.getElementById("demo").value = date;

  }
  </script>
  </body>
</html>

Upvotes: 1

Views: 198

Answers (1)

Tanaike
Tanaike

Reputation: 201438

How about this modification? I think that there are several answers for your situation. So please think of this as one of them.

Modification points:

  • In your showing script, there is GAS script in HTML. GAS cannot be directly run in HTML.
    • In this case, I used google.script.run.
    • The GAS script in getDate() was moved from ".html" to ".gs".

Modified script:

Please modify getDate() in ".html" file to the following script.

function getDate() {
  google.script.run.withSuccessHandler((e) => {
    document.getElementById("demo").value = e;
  }).getDate();
}

Next, please add the following function to ".gs" file.

function getDate() {
  var ss = SpreadsheetApp.openById('1CtH3uNeSiJcRd_ZKE3iWp7TEtAO_B7uCNatGJ7rHMeg');
  var monthly = ss.getSheetByName('MONTHLY');
  var date = monthly.getRange(1, 1).getValue(); //The value of A:1 = 10/12/2000 date
  return Utilities.formatDate(date, Session.getScriptTimeZone(), "YYYY-MM-dd");
}

Note:

  • ".gs" file is one of scripts in a project you use. I think that it is used to open the dialog.
  • If the same function names are in ".gs" file, please modify it.

References:

Upvotes: 1

Related Questions