How to create multiple rows in a sheet based on a number value in a form

[Chris Dinger]

Hello there,

I am creating a google site to help connect donors to local restaurants and charities to help keep our local restaurants afloat during this difficult time as well as provide meal vouchers to the many people who have been laid off due to the current situation using local charity participation.  The idea is that a donor calls a restaurant and says they want to purchase a # of meal vouchers.  The restaurant takes the payment, fills out a google form, with the # of vouchers and the charity they want to support.  The form will then populate a sheet with the information and a doc is created for each voucher.  

Issue:  I am using Document Studio to do the auto doc generation and it was working fine when I input one record from the form at a time.  However, sometimes people will want to order multiple vouchers and we don't want to have to enter into the form multiple times.  The idea is to use a '# of Vouchers' variable from the form and enter that number of rows into the sheet so we can create individual vouchers to be passed out.  Our script is acting 'wonky' and the first entry works fine (Say, Bob orders 5 vouchers.  The script creates 5 rows as expected.)  The problem is then when we go in and fill out another form and say Fred orders 6 vouchers.  The form puts the new record (Fred) under the originally created record for Bob and the script adds new records for Bob and not Fred.  Screen shots from the results.  First is after Bob's original form submit and second is after Fred's form commit.

Here is our script:

function populateVouchers() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var data = sheet.getDataRange().getValues();

  

  var lastRow = data.length -1;

  

  var numVouchers = data[lastRow][7] - 1;

  

  while (numVouchers > 0) {

    numVouchers--;

    sheet.appendRow([data[lastRow][0],

                   ,// Skip the ID Column. It is populated by a formula

                   data[lastRow][2],

                   data[lastRow][3],

                   data[lastRow][4],

                   data[lastRow][5],

                   data[lastRow][6],

                   data[lastRow][7],

                   data[lastRow][8]]);

  }

}

Can anyone help?  It is probably clear that I am NOT a developer. :)  I am technical and just trying to do something simple quickly to support our community before any more of our small local restaurants close.  (They are all only doing pickup/delivery.) I appreciate any guidance you can provide!!  

Chris

[CBM Services]

Hi Chris,

From the script, it seems to be reading the spreadsheet, then re-writing it back to the same spreadsheet starting at lastrow-1 not sure why you are doing that.

However, I can give you a hand if you want.

Send me an email at cbmwebs...@gmail.com and I can try to help.

---

From: Chris Dinger
Sent: ‎2020-‎03-‎28 9:15 AM
To: Google Apps Script Community
Subject: [Apps-Script] How to create multiple rows in a sheet based on anumber value in a form

--
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/8b03f77e-9715-4d3d-a7d6-0789a5e0448d%40googlegroups.com.

[CBMServices Web]

Oops I need to spell my email correctly..

cbmserv...@gmail.com

[Kim Nilsson]

Sometimes convenience makes simple things unnecessarily hard.

It's really not too much to ask for a donor to fill in your form more than once.

[Alan Wells]

Is the Form linked to the spreadsheet, and are you editing previous submissions?  If you edit a previous submission, then the original row in a LINKED sheet will be overwritten. 

[Chris Dinger]

Thanks George, I will send you an email!

Chris

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Chris Dinger]

I hear you, Kim and that was my original thought.  We are not accepting payments on our site, so the process is that the donor calls the restaurant and they take payment, fill in the form.  While entering a few times would be reasonable, we actually had a business donate 50 vouchers to a small restaurant. (The business called the restaurant and the restaurant manually created and is tracking the voucher numbers.  We are dealing with mom and pop shops, and are just trying to easily connect donors, restaurants, and charities (who hand out vouchers to folks, including homeless teens who don't have access to internet, hence being able to create printable vouchers.)

Chris

[Chris Dinger]

Hi Aj,

Currently, the Google Form creates a row for each response in the Google Sheet.  My dilema is that I want to create one entry point (form) that can ask the # of vouchers as a question and then create that # of rows in the spreadsheet.  I need a separate row in the spreadsheet for each voucher because I am using Document Studio add-on to create a PDF that is put in a folder for each charity.  

To simplify and talk requirements: 

• Donor calls restaurant and provides # of vouchers they want to buy and charity they want them to go to.
• Restaurant collects payment (using their own payment system) then enters donor name, # vouchers, charity into the form.  
• Form creates record to provide the restaurant a list of their voucher numbers and associated charity
• PDF is created for each voucher and that is placed into a Google Drive folder that the charity can access

Using Google Sites, Form, Sheet and Doc,  I have it all working for a single voucher.  

I know there are much more eloquent ways to do this process than what I created, but I was going for easy (low web dev experience) and free/cheap.  I'm just doing my best to step up and support my community with my limited dev skills!  :) 

Chris

[Kim Nilsson]

Ah, I misunderstood the process!

Each voucher has to be put into the Form one by one!

Now I get it. Yes, that's too much for anyone.

But, why not do that after the fact?

Fill in the form once for each donor, "50 vouchers, please".

Then have scripts manage voucher creation in a different sheet/tab, so it doesn't mess with the incoming form data?

If the script creates vouchers with appendRow it shouldn't touch previously created voucher rows?

And, like AJ said, don't ever touch previous Form submissions!

[Chris Dinger]

Ok, thanks, I'll look into how to do that!  That makes sense.  

Chris

[Alan Wells]

You can set the Form to not allow edits.  The users of the Form probably won't know what the code is doing or what effect editing a previous submission has.

Also, I've seen situations where having editors/collaborators to the Google Form can make the code do strange things.  Sometimes the code runs for each user.

[Michael O'Shaughnessy]

I think Kim has a good suggestion...   You can easily create a trigger to have multiple rows added to another sheet then have the vouchers created from there.  I did something like this for when awarding certificates if they get a number of questions correct.  It would send info to another spreadsheet and crank out a certificate.

--

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/d4ecfd9a-ad14-41a6-aff7-64edbfe28865%40googlegroups.com.

[Chris Dinger]

Thanks everyone, creating a function to add rows to another sheet using the # of vouchers as a variable for the number of rows to create worked! The vouchers are successfully being created on the secondary tab. I really appreciate the input from this forum.

Our local chamber of commerce is intrigued and will be discussing the idea of our little web form/vouchers in their next meeting, so it would be amazing if we can get some legs on this and really help our community!! :)

Chris