Submitting data to different tabs using google sheet script editor

Question

In my spreadsheet script editor, I have the below codes:

Code.gs

function doGet() {
    return HtmlService.createTemplateFromFile('checkForm.html')
}

function doPost1(e) {

  Logger.log(JSON.stringify(e))
  if (!e || !e.parameter) {
    return;
  }
  var lock = LockService.getScriptLock();
  lock.tryLock(10 * 1000);
  var scriptProp = PropertiesService.getScriptProperties();

  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var checkForm = ss.getSheetByName("checkForm");
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1;
    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header]
    });
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    var startTime = newRow[1];
    var endTime = newRow[2];
    var cal = CalendarApp.getCalendarById("ID");
    var allEvents = cal.getEvents(new Date(startTime), new Date(endTime));
    if (allEvents.length > 0) {
    return HtmlService.createTemplateFromFile('calendarAgenda.html')
    }else {
    return HtmlService.createTemplateFromFile('bookingForm.html')
    };

  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

function doPost2(e) {

  Logger.log(JSON.stringify(e))
  if (!e || !e.parameter) {
    return;
  }
  var lock = LockService.getScriptLock();
  lock.tryLock(10 * 1000);
  var scriptProp = PropertiesService.getScriptProperties();

  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var bookForm = ss.getSheetByName("bookForm");
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1;
    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header]
    });
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);    

    return ContentService
    .createTextOutput(JSON.stringify('Successfully received. Thank you!'))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

checkForm.html

<!DOCTYPE html>
<body>
<form name="class1Check" id="class1Check" action="ScriptURL" target="_self" method="POST">
Start Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="Start Date & Time">
<br><br>
End Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="End Date & Time">
<button type="submit" onclick="google.script.run.doPost1(this.parentNode)">Check</button>
</form>
<script>
function postData(form) {
  google.script.run.withSuccessHandler(postData).doPost1(e);
}
</script>
</body>
</html>

bookingForm.html

<!DOCTYPE html>
<body>
<form name="class1Booking" id="class1Booking" action="ScriptURL" target="_self" method="POST">
<inputs> ..
<button type="submit" onclick="google.script.run.doPost1(this.parentNode)">Check</button>
</form>
<script>
function postData(form) {
  google.script.run.withSuccessHandler(postData).doPost2(e);
}
</script>
</body>
</html>

The goGet function returns the "checkForm.html" page that when submitted is supposed to run the doPost1 function to send the data to tab "checkForm" in the spreadsheet and then returns the second page "bookingForm.html" that when submitted is supposed to run the doPost2 function to send the data to tab "bookForm" and then returns a certain text output

When I submit the check form, I receive the error "Script function not found: doPost" and I think I might have some issues with google.script.run that I tried to modify several times with no luck. Any help and thanks in advance

I don’t think that renaming to do post functions like that work at all but I’ve never tried it so I don’t really know that. — Cooper, Feb 17 '20 at 14:41
What’s the point of your e parameters in your do post client side? I don’t think doposts were meant to be used with google.script.run — Cooper, Feb 17 '20 at 14:50
In different cases when I was dealing with only one form, I don't use google.script.run. Only put the script URL in the form action and the doPost sends the data to the sheet. Now I have two post cases in two different forms that I need the script to differentiate between the both of them. When I searched it, I thought I may use google.script.run in order to run two doPost functions (one for each form) based on changing the function name. — Cen Eg, Feb 17 '20 at 15:04
Using one doPost function with the variables of the "checkForm" tab in the spreadsheet, I can get the first form to work but what about the second form? — Cen Eg, Feb 17 '20 at 15:14
I'm thinking of another probability to include IF in the doPost like that; IF form Id=".." is submitted, try { } and IF form Id=".." is submitted, try { }. What do you think? — Cen Eg, Feb 17 '20 at 15:22

Cooper · Answer 1 · 2020-02-17T17:08:30.107

0

Forget about the doPost(). Try something like this.

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <style>#msg{display:none;}</style>
  </head>
  <body>
    <form>
      <!--><inputs type="text" name="name1">--> 
      <button type="button"  value="Submit" onclick="processForm(this.parentNode);" />
      </form>
 <div id="msg"></div>
 <script>
   $(function(){
     //You can get stuff from the server after the dom has loaded.  I know it takes some time but it's a simple way to intialize.  Personally for my own sutff Id rather do this that use templated html
   });

  function processFormC(form) {
    //input validation
    google.script.run
    .withSuccessHandler(function(obj){
      $('#msg').css("display","block");
      $('#msg').html(obj.msg);
    })
    .processFormS(form);
  }
</script>
  </body>
</html>

GS:

function processFormS(obj) {
  //obj.name1...
  return {msg:'Got it.'}
}

function doGet() {
    return HtmlService.createHtmlOutputFromFile('whatever file name')
}

And personally I prefer putting all of the javascript and css in the same file because it's easier to find later on. Yes I know it can get large but again that's my choice it doesn't have to be your choice.

edited Feb 17 '20 at 17:08

answered Feb 17 '20 at 16:56

Cooper

59,616
6
23
54

What is **C** in `function processFormC(form) {` in HTML file – Cen Eg Feb 17 '20 at 17:35
I put a C in there for clientside and an S in the server side. A lot of times I just make them the same. That's one case where functions can have the same name because one is on the server and one is client side javascript. – Cooper Feb 17 '20 at 17:40
I'm trying to use your solution but still not figured it out. I'm supposed to have two forms but in your HTML only one existed. I'm sorry I'm not very good at coding and things seem difficult to me – Cen Eg Feb 17 '20 at 18:04
Well, I wasn't trying to provide a complete solution. I was just trying to show you a technique that could work for you because clearly the way you were going was not going to work. – Cooper Feb 17 '20 at 18:08
So are the two forms on the same page or different pages? – Cooper Feb 17 '20 at 18:09
You said `I'm trying to use your solution but still not figured it out. I'm supposed to have two forms but in your HTML only one existed. I'm sorry I'm not very good at coding and things seem difficult to me` . Don't feel bad the first time I tried to learn Google Apps Scripting I finally gave up and I don't give up anything easily. I actually started to believe that the Google people were actually much smarter than the rest of us. But the new documentation that they have and support now is really much better and I found it a lot easier the second time. And SO has been a great blessing. – Cooper Feb 17 '20 at 18:46
Thank you Cooper for continuous support. In my project I have 4 files; 1 script file "Code.gs" and 3 HTML files "checkForm", "bookingForm" and "Agenda". "Code.gs" contains doGet to display "checkForm.html" and doPost with if/else statement based on the sumitted data from "checkForm.html". So after submitting the form in "checkForm.html" we have two cases; either returns "bookingForm.html" or returns "Agenda.html" . Till this point I'm okay but the problem is that "bookingForm.html" has another form so we have two forms in two different HTML files. – Cen Eg Feb 17 '20 at 20:48
For the form in "checkForm.html" I can put the script URL in the form action and the sheet variables in the doPost to define the new submitted row and it works just fine at which it gives me the submitted data in the sheet and redirects the user based on the if/else statement but what about handling form responses from "bookingForm.html" – Cen Eg Feb 17 '20 at 20:52
I think I can answer your question better if you can append to your question the code that you have now so I can see what you've got. – Cooper Feb 17 '20 at 21:00
Of course, see everything [here](https://docs.google.com/spreadsheets/d/1h7y4RPOviX7_nzqWZRF2_eayzMda22X3wIyEXS7vXCM/edit?usp=sharing) – Cen Eg Feb 17 '20 at 21:10
I'm sorry but I don't follow links to spreadsheets off of this site any longer. – Cooper Feb 17 '20 at 21:25
It's okay I will update the question with the entire code – Cen Eg Feb 17 '20 at 21:35
The question is updated with the entire code and in the spreadsheet I have two tabs; one to receive the submitted data from "checkForm.html" and the other one to receive the submitted data from "bookingForm.html" – Cen Eg Feb 17 '20 at 21:48

score 0 · Accepted Answer · answered Feb 18 '20 at 10:20

You’re not using google.script.run functions correctly, as it’s stated in the documentation you can only return values of types:

Number, Boolean, String, or null, as well as JavaScript objects and arrays that are composed of primitives, objects and arrays.

An in your case, you’re trying to return a textOutput object, which is not allowed, this class is intended for Web Apps functions (doGet(e) or doPost(e)).

In the documentation about [Client-to-Server communication] (https://developers.google.com/apps-script/guides/html/communication) it’s explained how to work with google.script.run. Here is an example applied to your case so you can understand better how it works:

checkForm.html

<!DOCTYPE html>
<body>
<form name="class1Check" id="class1Check" target="_self" method="POST">
Start Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="Start Date & Time">
<br><br>
End Date & Time
<input class="w3-input w3-border" type="datetime-local" required name="End Date & Time">
<button onclick="postData(this.parentNode)">Check</button>
</form>
<script>
//It’s run when form button is clicked 
function postData(form) {
   //Calls doSomething function in code.gs with form parameter
  google.script.run.withSuccessHandler(handlerFunction).doSomething(form);
}

//Handles the response from doSomething function
function handlerFunction(responseData) {
  //Logs ‘It worked!’ in developer console
  console.log(responseData);
}
</script>
</body>
</html>

code.gs

//Web App function
function doGet() {
    return HtmlService.createTemplateFromFile('checkForm.html')
}

//Apps script function to receive form object and return response
function doSomething(form) {
//Do something
  return ‘It worked!’;
}

Apps Script’s Web Apps are designed to be a single page application (one HTML page) and not a multi-page application, although there are different workaround examples you could guide from to achieve a multi-page behavior:

[1] Linking to another HTML page in Google Apps Script

[2] https://sites.google.com/corp/view/googlappsscript/recent-scripts/multiple-page-webapp-example

Submitting data to different tabs using google sheet script editor

2 Answers2