Keeping leading zeros from a google form to google result spreadsheet

[Sheryl Anderson]

We are using a Google form to collect tuition contact information for our high school registration. In the Google form, there is a text field that collect Routing numbers and Bank account numbers. When you look at the results of those fields in the Google sheet, the leading zeros are gone for both fields. The routing number is easy to format with leading zeros. However, since bank account numbers are not a set number of characters, I have no way of knowing how many leading zeros to format the cells. I need it to keep what the user has input into that field. 

I have tried pre-formatting the field in the spreadsheet as text, but the input from the form overrides that as a number. (even though the field on the form is setup as a text input)

Kicker is that this worked last year. (most likely with the OLD google sheets)  Anyone know of a way I can get around this problem?

Also, with google forms, how do I turn off the auto populate feature in a google form now?  I don't want the next person using the form to see another person's banking information.

Any help with these 2 items would be great.

thanks,

Sheryl Anderson

De La Salle Collegiate High School 

Technology

[Wurdock, Tom]

I don't have a solution yet but I have recently started working on the leading zeroes issue as well. If I get a promising lead or solution I will respond again. 

Tom

_________________________________________

Tom Wurdock Senior Web Developer ITS Office: (651) 523-2409 twurd...@hamline.edu www.hamline.edu

--
You received this message because you are subscribed to the Google Groups "Apps User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to appsusergrou...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mark Allen]

If you were doing this manually, you'd be able to precede the numerical value with an apostrophe (single quote or ' ), so I guess you could: 

• force the addition of that by using another column where you take your column and use the =CONCATENATE formula. So if your numbers are in column B and start in row 2, 
• insert a column in C with the instruction =CONCATENATE("'",B2) 
• copy that into all the cells in column C.  (That bunch of marks, by the way is a single quote surrounded by double quotes because it's a text field.)
  

If that doesn't make sense, holler and I'll share an example with you...

Mark

​Director of Transformational Technology, Delegated Services CIC

​

Chief Balloonist, EdintheClouds

​plus.google.com/​

+MarkAllen

@edintheclouds

(44) 07798 745332

   

[Anderson, Sheryl]

Mark,

That makes perfect sense. I have used the Concatenate command before, however, when the user hits 'submit' on the Google form, the data turns into a number. I have no way of knowing how many leading zeros were typed in, therefore, wouldn't know what to concatenate.  It will be a huge bummer if  Google took this feature out of forms. (pre-formating a column so the data looks as how the user typed it)

Sheryl Anderson

Technology

De La Salle Collegiate High School

DLS Technology Website

Twitter: @DLS_Tech

[Mark Allen]

Yes, Sheryl, but you know that the number will be in column B, right?  So you can put a quote in front of it however many zeros there are.  I take it you still want to be able to see the zeros?

M

​Director of Transformational Technology, Delegated Services CIC

​

Chief Balloonist, EdintheClouds

​plus.google.com/​

+MarkAllen

@edintheclouds

(44) 07798 745332

   

[Bjorn Behrendt]

If you know the amount of characters you can forcibly put the 0's in-front, in another column, using

=ArrayFormula(text(B2:B,"00000"))

Bjorn Behrendt M.Ed ~ Never Stop Learning

   b...@askbj.net | (802) 772-0003

   Blog: Edlisten.com

[Wurdock, Tom]

I am finding that when reading the cells from app script the values are still zero-stripped numbers. I can get the zero padding to show up in sheet view but from code we might need to pad as we go.

Tom

_________________________________________

Tom Wurdock Senior Web Developer ITS Office: (651) 523-2409 twurd...@hamline.edu www.hamline.edu

[Anderson, Sheryl]

Unfortunately, with bank account numbers,they can be any length so I can't force zeros in front. (I can on the Routing number because those are always 9 digits.)

Sheryl Anderson

Technology

De La Salle Collegiate High School

DLS Technology Website

Twitter: @DLS_Tech

[Bjorn Behrendt]

The form results have the leading 0's, so at the very least you have the correct data available to you.

If you want to get fancy it probably could be scripted to auto-correct the sheet on Form Submit, but that could become a big project.

You also might want to look at a more secure form builder like http://www.jotform.com/, since you are collecting bank information.    JotForm is nice that it can integrate with Google Apps. 

Bjorn Behrendt M.Ed ~ Never Stop Learning

   b...@askbj.net | (802) 772-0003

   Blog: Edlisten.com

[Wurdock, Tom]

Ah yes, we both need the original zeroes to be left alone.

_________________________________________

Tom Wurdock Senior Web Developer ITS Office: (651) 523-2409 twurd...@hamline.edu www.hamline.edu

[Anderson, Sheryl]

Bjorn,

That's my problem. With the new Forms and Sheets, it drops the leading 0's in the results spreadsheet after a Submit. It changes the Text input from the form automatically to a number, losing the leading zeros in the process.

Sheryl Anderson

Technology

De La Salle Collegiate High School

DLS Technology Website

Twitter: @DLS_Tech

[Bjorn Behrendt]

It can be scripted.    

Form:  https://docs.google.com/forms/d/12XIQ2LHygciAR5239oCFzZysRW5Avg2gv-0M7ZLrfS4/viewform

Results Sheet: https://docs.google.com/spreadsheets/d/1GXyfQyTRtbMIDuVOfZlLqkLJzigSUmgZciE7f7Icr7g/edit?usp=sharing

Code I used in the form:

function checkResponses() { // I set this to run onFormSubmit

  var form = FormApp.getActiveForm();

  var responses = form.getResponses();

  var responcesL = responses.length;

  var lastResponce = responses[responcesL-1];

  var item = form.getItemById("1946268316");

  var value = lastResponce.getResponseForItem(item).getResponse();

  checkSpreadsheet(value);

}

function checkSpreadsheet(value){

  Logger.log("ran");

//  value = "000344";

//  value = Utilities.formatString("%012d", value);

  var form = FormApp.getActiveForm();

  var destID = form.getDestinationId();

  var sheet = SpreadsheetApp.openById(destID).getSheetByName("Form Responses 1");

  var row = sheet.getLastRow();

  var column = 2;  //colum that corrosponds to the result

  var range = sheet.getRange(row, column);

  range.setNumberFormat("@STRING@");

  range.setValue(value);

}

function whatItems(){  // This is only used to get the item id that is entered in the  checkResponses() function.

 var form = FormApp.getActiveForm();

 var items = form.getItems();

  for (i in items){

   var itemID = items[i].getId();

    var itemTitle = items[i].getTitle();

    Logger.log(itemTitle +" - "+itemID);

  }

  

  

}

Bjorn Behrendt M.Ed ~ Never Stop Learning

   b...@askbj.net | (802) 772-0003

   Blog: Edlisten.com

[Wurdock, Tom]

That's great. I am wondering if there is a way to leave the strings as strings in the spreadsheet without HAVING to use script? 

_________________________________________

Tom Wurdock Senior Web Developer ITS Office: (651) 523-2409 twurd...@hamline.edu www.hamline.edu

[Bjorn Behrendt]

Not until Google fixes newly added rows keeping the format set for the column.   Forms doesn't use the next available row, it actually inserts a new row on form submission.   

Bjorn Behrendt M.Ed ~ Never Stop Learning

   b...@askbj.net | (802) 772-0003

   Blog: Edlisten.com

[Wurdock, Tom]

Thank you Bjorn.

_________________________________________

Tom Wurdock Senior Web Developer ITS Office: (651) 523-2409 twurd...@hamline.edu www.hamline.edu

[Bjorn Behrendt]

Can you clarify the auto populate feature.   The only thing that I know of is the built in feature of all browser to save form fields, but that is per user, so unless you have people filling out the form while signed into the same account in the browser, that should not matter.   If they are sharing computers then I suggest opening up the form in an incognito window.

Bjorn Behrendt
http://www.EdListen.com/
http://www.AskBj.net/
http://www.VTed.org/

--

[Ethan Gold]

Still having this problem.

This is so utterly basic.

Forcing data validation with a regexp doesn't trigger preservation either.

Collecting international zipcodes is failing.

Collecting passport information is failing ([0-9A-Z]+).

How is this not top priority? A huge percentage of form users must be collecting shipping and contact information and you may depend on users to do the obvious thing they do everywhere else: enter the number only.

Google, please fix this!! Don't you eat your own dogfood?!?

[Infante, Angela]

You can format cells by clicking on the numbers in the toolbar and selecting "More Formats" and then "Custom Number Formats."

--

You received this message because you are subscribed to the Google Groups "Apps User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to appsusergrou...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Sincerely,
Angela Infante

--------------------

Angela Infante
Evergreen Local Schools
Director of Technology
14544 County Road 6
Metamora, OH 43540
419-644-3521 ext. 1115

ainf...@evgvikings.org

--------------------

CONFIDENTIALITY NOTICE:

The contents of this email and its attachments contain confidential and/or legally privileged information which is for the use of the intended recipient only. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or the taking of any action in reliance on the comments of the contained information is strictly prohibited and that the documents should be returned to the sender immediately. If you have received this email in error, please notify me by email immediately.

[Wurdock, Tom]

I have wrestled with this in the past. If I remember correctly, setting the format on a cell or column does not affect the format of new values that come in from the form. Google wants very much to make a number-ish value into a number.

Ethan, does the column format change anything?

In our case we knew we were getting 4 digit numbers with leading zeroes from the user so we had to use a script to set the cell format and add leading zeroes AFTER the form was submitted. Maybe something similar could be done. Set the format and add the original value...

Tom

_________________________________________

Tom Wurdock Senior Web Developer ITS Office: (651) 523-2409 twurd...@hamline.edu www.hamline.edu

[Bjorn Behrendt]

I updated my script.   You can add this to the results sheet, then set the onFormSubmit trigger.   As long as you identify what question you need to input as a string, it should work.   Basically the result coming form the form itself, includes the 0's, but the spreadsheet auto-formats them.  This script, just forces that cell to be a string, and then pastes the value back into that cell as a string.

var itemId = "820613007";  // use the whatItems() function to find out what is the id of the question you want to have the 0's entered for.

var column = 2; // this is the column number of where the results go to in the spreadsheet.

function onFormSubmit_Leading0s(e){   // To to the resources menu and set this to run onFormSubmit();

  var source = e.source;

  var range = e.range;

  var sheet = range.getSheet();

  var row = range.getRow();

  

  var formURL = source.getFormUrl();

  var form = FormApp.openByUrl(formURL);

  var responses = form.getResponses();

  var responcesL = responses.length;

  var lastResponce = responses[responcesL-1];

  

  var item = form.getItemById(itemId);

  var value = lastResponce.getResponseForItem(item).getResponse();

  

  var range2edit = sheet.getRange(row, column);

  range2edit.setNumberFormat("@STRING@");

  range2edit.setValue(value);

}

function whatItems(){  //used only to get the ItemId's of the questions needing to have trailing 0's

 var source = SpreadsheetApp.getActiveSpreadsheet();

  var formURL = source.getFormUrl();

  var form = FormApp.openByUrl(formURL);

 var items = form.getItems();

  for (i in items){

   var itemID = items[i].getId();

    var itemTitle = items[i].getTitle();

    Logger.log(itemTitle +" - "+itemID);

  } 

}

Bjorn Behrendt M.Ed ~ Never Stop Learning

   b...@edlisten.com | Blog: Edlisten.com

[Guido Pettinari]

Hello!

I have written a Google Script based on Bjorn's that coverts all answers of a Google Form to plain text, without the need to specify item IDs or column numbers. The script fixes all auto-formatting problems, including the leading zeros being stripped from Google Form's answers.

You can find the script on my Github: https://gist.github.com/c578f49aa5f6f28f76a6a8678e20c423

Please feel free to ask for any doubts :-)
Cheers,

Guido

PS: Instructions are in the file, but I write them down here as well:

This is a Google Script and it needs to be attached to a Google

Spreadsheet:

1) Open the Google Spreadsheet linked to the Google Form, and

   go to Tools -> Script editor.

2) Paste the content of this file in the editor and save.

3) Attach this function to the onFormSubmit trigger using the

"Edit -> Current project's triggers" menu.

[stages...@gmail.com]

[Wayne G]

I think the problem is that Google sheet (like Excel) wants to treat anything that looks numeric as a number.  When I downloaded the information to my computer as a csv file, it kept all the fields as text fields, e.g. "000012345" showed up fine.  It was only when I tried to open it directly in Excel that I lost the leading zeros.  So you could try downloading it to csv and working with it locally - or insert a leading or trailing "X" in those fields so that the spreadsheets won't try treating them as numbers.

On Friday, February 27, 2015 at 9:18:24 AM UTC-5, Sheryl Anderson wrote:

[Christopher Rehn]

Hello! We've gone with using a "regular expression doesn't match" and simply relying on people to understand it.

^0[0-9]+

This makes any number starting with 0 unavailable, and a text pops up encouraing users to open with an apostrophe ( '003123123 ) which will then keep the zeros when exported to Sheets.