Why the text appears deformed and how can i make the script run every time the form is opened?

22 views
Skip to first unread message

Edoardo Frulla

unread,
Jun 26, 2022, 5:51:17 AM6/26/22
to Google Apps Script Community
Guys this is the script

function insertImage(){

var form = FormApp.getActiveForm();
var items = form.getItems();
var item = items[0];

var sheet = SpreadsheetApp.openById(<my spreadsheet>);
var stringa = "";
var data = sheet.getDataRange().getValues();
for (var j = 1; j < data.length; j++) {
var maschio = (data[j][3].toString());
var femmina = (data[j][7].toString());

if (j == data.length -1){
stringa = stringa.concat(maschio, "-", femmina);
} else {
stringa = stringa.concat(maschio, "-", femmina, "\n");

}
}
item.asSectionHeaderItem().setHelpText(stringa);

}

The text i modify with the last command appears deformed, how can i fix this?

Moreover, how can i make the script execute every time the form is opened??

Clark Lind

unread,
Jun 26, 2022, 8:43:54 AM6/26/22
to Google Apps Script Community
The second question is easier to answer, so I'll start with that. lol   You can add an onOpen(e) trigger to your script.
For the first part, can you explain more about what you mean by "deformed"? Maybe a screenshot?

Edoardo Frulla

unread,
Jun 26, 2022, 8:51:11 AM6/26/22
to Google Apps Script Community
Ty, but i need that everyone (not only that is able to edit) can trigger the script.

Regarding the first question, it seems that it is fixed on mobile and it is good.

Clark Lind

unread,
Jun 26, 2022, 9:00:15 AM6/26/22
to Google Apps Script Community
Sorry, I forgot that was for editing the form, not filling it out.  Hmmmm..... let me give it some thought.

Clark Lind

unread,
Jun 26, 2022, 9:03:43 AM6/26/22
to Google Apps Script Community
Ok, you might be able to create a simple webapp. Give users the webapp link instead of the Form link (url). 
In the doGet() function, do all your Form work and prep, and then have javascript in the webapp open the Form url.   Just thinking as I type...  I don't know what (if any) limitations you might run into.

Jonathan Butler

unread,
Jun 26, 2022, 10:08:55 AM6/26/22
to google-apps-sc...@googlegroups.com
I think Clark's solution is probably the best.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a9346664-286c-4541-8f40-c4a5e424ce62n%40googlegroups.com.

Clark Lind

unread,
Jun 26, 2022, 10:57:25 AM6/26/22
to Google Apps Script Community
This was fun trying to get it to work... 
From Code.gs:
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('formPreloader');
  return template.evaluate()
    .setTitle('fetching form...')  //change this to the page title you want
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function insertImage() {
  var form = FormApp.getActiveForm();
  var items = form.getItems();
  var item = items[0];
  var sheet = SpreadsheetApp.openById("<my spreadsheet>");
  var stringa = "";
  var data = sheet.getDataRange().getValues();

    for (var j = 1; j < data.length; j++) {
        var maschio = (data[j][3].toString());
        var femmina = (data[j][7].toString());
          if (j == data.length -1){
            stringa = stringa.concat(maschio, "-", femmina)
          } else {
            stringa = stringa.concat(maschio, "-", femmina, "\n")
            }
    }

    item.asSectionHeaderItem().setHelpText(stringa);
    return form.getPublishedUrl();
}

Create new html file:  
From formPreloader.html:
<!DOCTYPE html>
<html>

<body>
  <H3>Fetching form...</H3>
  <script>
    $(function() {
    // Call the server here to retrieve any information needed to build the page.
  
    google.script.run
       .withSuccessHandler(function(response) {
            // Respond to success conditions here.
            loadForm(response);
          })
       .withFailureHandler(function(msg) {
            // Respond to failure conditions here.
          console.log(msg)
          })
       .insertImage();
  });

  function loadForm(url) {
    window.location.assign(url)
  }
  </script>
</body>
</html>

Something like the above works to open an html page, the javascript on that page calls back to the server to run the form update function which updates the form, then receives the form's published url back. When it receives the form's url, it then navigates to the updated form.
Reply all
Reply to author
Forward
0 new messages