1

I have a Form that my staff uses to send me data through out the day.

One of the sections is a " long answer text" box for any comments.

Usually there are no comments so it is left blank.

My question is -

is there a way to automatically pre fill text in a google form and how?

I have used the pre filled link but if the user decides to submit another form (which they always do), it will take them back to the original form link and not the pre fill link.

This data is sent to a spreadsheet with a custom dashboard with the most recent data.

If there is a blank in the comment column, it will grab the last non blank cell in the column section and give off the wrong comment for that data.

I havent found a way to fill all the blank cells with "no comment" in google sheets without filling all the cells that have not been used for google form submissions. Figured it might be easier to start with any script magic from google form instead of google sheets.

Thank you in advance

Alexander Ibarra
  • 1,263
  • 2
  • 9
  • 10

2 Answers2

0

Yes this is possible, and you can use a separate spreadsheet as input for your pre-filled form that you don't have to change.

There is already an answer for a question that has a lot of commonality with yours here.

Have a try with that first. If you tried to script it and cannot go further you can submit your tried code here and we can help you further. Always try it yourself first before asking the question here at SO.

LTi-GT
  • 62
  • 10
0

A couple of ideas.

First, you wrote:

I have used the pre filled link but if the user decides to submit another form (which they always do), it will take them back to the original form link and not the pre fill link.

To deal with this, remove this option in the forms settings, within the presentation tab. Then your users will only be able to use the pre-fill link you have provided them with.

Secondly to handle the situation where a user removes your prefilled text (or they find the form link that isn't prefilled and use that instead) you could write a script that gets triggered on a form submission to check this value and replace any blanks with "no comment".

The script should be attached to the spreadsheet that collects the form response.

Here is some rough code that might work for you...

function onFormSubmit_noComments(e) {
  var col = 5 //replace this with your column number for the comments field (1st column is 1)
  var row = e.range.getRow(); //The row in the spreadsheet that was just inserted
  var commentsCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(row, col);
  var answers = e.range.getValues();

  if (!answers[0][col - 1]) {//this will fire if the field was left blank
     commentsCell.setValue("No Comment");   
  }
}
DavidJCC
  • 78
  • 6