CODEWITHSUNDEEP
google-apps-script
Ben
Ben

Reputation: 353

Form creating entries *before* entries from script

I have a sheet which is populated by an expenses form.

Each time the form is submitted, a new row is created in the spreadsheet.

I also have a small script, which I run on the first of the month to add a few lines to the same sheet (some regular expenses that don't need me to use the form).

This all works fine.

The problem is that after I run the script (which adds about 5 lines to the sheet), when I go back the form, and submit it. New entries are being put ahead of the scripts ones.

So:

1) Submit expenses form -> new entry in row 2.
2) submit expenses form -> new entry in row 3.
3) run script -> new entries in rows 4,5,6,7,8,9
4) submit expenses form -> new entry in row 4. Rows 4,5,6,7,8,9 shift down one

Can anyone advise?

Upvotes: 0

Views: 58

Answers (2)

Umair Mohammad
Umair Mohammad

Reputation: 4635

Why is this happening ?

Actually, I think google form internally keeps a track of last row number where it have inserted your response. So that next time it can decide where to put your new response.

So when you're manually[by script] entering data in sheet and not through google form so the counter of google form row number is not updated and that's unaware of the fact that you have inserted some data, so it tried to insert data at the same position that it remembers.

Hope this will help you, at least to some extend.

How to solve this ?

Solution to your situation would be, as mentioned in another answer; you can use the FormService to submit your data as "form responses" and thus the google form internal counter also increases it's count. Everything is in sync.

Upvotes: 2

Diego
Diego

Reputation: 9571

Since your script is not doing any manipulations to the data, but simply adding recurring values to the form, use the Forms Service to submit your data to the form directly as responses. With this approach, the expenses will all live within the Form responses in case you ever disconnect the form from the sheet. 

function addRecurringExpenses() {
  var form = FormApp.openById("FORM_ID");

  // Create an array of objects where each object represents one of your recurring expenses.
  // The key-value pairs correspond to the questions in the fom
  var recurringExpenses = [{"question1": "1-response1", "question2": "1-response2", "question3": "1-response3"},
                           {"question1": "2-response1", "question2": "2-response2", "question3": "2-response3"},
                           {"question1": "3-response1", "question2": "3-response2", "question3": "3-response3"}]
  var questions = form.getItems(); // Get all of the questions in the form
  for (var i=0; i<recurringExpenses.length; i++) { // Loop through the recurring expense
    var response = form.createResponse(); // Create a new response that will house all of the answers for this one expense

    var question1 = questions[0].asTextItem(); // Not all questions are "TextItems" – https://developers.google.com/apps-script/reference/forms/
    var answer1 = question1.createResponse(recurringExpenses[i]["question1"]);

    var question2 = questions[1].asTextItem();
    var answer2 = question2.createResponse(recurringExpenses[i]["question2"]);

    var question3 = questions[2].asTextItem();
    var answer3 = question3.createResponse(recurringExpenses[i]["question3"]);

    // Add each answer to the response object
    response.withItemResponse(answer1).withItemResponse(answer2).withItemResponse(answer3);
    response.submit(); // Submit the response to the form
  }
}

Upvotes: 1

Related Questions