What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    Sorting will not save in Integrated Google Sheet

    Asked by bdabbs on January 05, 2017 at 07:07 PM
    I have a google sheet that is integrated with a JotForm.  I have team members that need to sort and filter the data.  However, the data became jumbled when they sorted and filtered.  As a fix, I created a new sheet within the workbook using the =importrange() function to import the data.  I figured this way they could sort and filter using real-time without corrupting the master sheet.  However, every time I try and sort the target sheet, it won't save.  It sorts for a second but then reverts back to the original layout?  Is there a way to sort this and make it save?
     
    Thank you in advance,
     
    Bryce
     
     
    Hi Mike, thank you for responding.  The "=importrange()" I used is 1:1000.  I expect at least 6,000 submissions in 2017.  That said, I started with 5,000 but then lowered it thinking it might solve my problem since the sheets start at 1,000.  Obviously it did not help with my sorting/filtering issue.  
     
    My formula is currently set at =IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxx","Submissions!1:1000"). Sorry, I had to use the "xxxx" rather than the source key for this thread because of the data being collected.  
     
    Employees use the form to request corporate checks.  Below is a partial snapshot of what the sheet looks like.  
     
     Inline image 1

    Once the jotform is submitted and the data populates, accounting needs to sort and filter the checks and dates by project, check number and submission date.  For example, They might filter down to one (1) project and then sort by date and then want to sort the check numbers in order.  
     
    Unfortunately when I sort or filter, the data almost immediately reverts back to it's original format.  I can't figure out how to get it to stay/save the way I sorted or filtered.  
     
    I appreciate any help you can offer.
     
    Thank you again,
     
    Bryce
  • Profile Image
    JotForm Support

    Answered by Mike_G on January 05, 2017 at 10:02 PM

    I understand that you're trying to sort and filter the original sheet that has all the data of your form submissions. I also understand that you have used the "=importrange()" to import the orignal data into a separate sheet so you can instead sort and filter it there since editing the original sheet will just break the integration.

    Did you import the data from the original sheet to the new sheet as a whole? It could be one of the reasons why it is reverting back to what it was even after you sort and filter it.

    Can you help us fully understand what you're aiming to achive by providing us more details, please? May we know what kind of sorting and filtering are you doing and the outcome that you would like to produce?

    We will wait for your response.

    Thank you.

  • Profile Image

    Answered by bdabbs on January 06, 2017 at 09:08 AM

    Hi Mike, thank you for responding.  The "=importrange()" I used is 1:1000.  I expect at least 6,000 submissions in 2017.  That said, I started with 5,000 but then lowered it thinking it might solve my problem since the sheets start at 1,000.  Obviously it did not help with my sorting/filtering issue.  

     
    My formula is currently set at =IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxx","Submissions!1:1000"). Sorry, I had to use the "xxxx" rather than the source key for this thread because of the data being collected.  
     
    Employees use the form to request corporate checks.  Below is a partial snapshot of what the sheet looks like.  
     
     Inline image 1

     

    Once the jotform is submitted and the data populates, accounting needs to sort and filter the checks and dates by project, check number and submission date.  For example, They might filter down to one (1) project and then sort by date and then want to sort the check numbers in order.  
     
    Unfortunately when I sort or filter, the data almost immediately reverts back to it's original format.  I can't figure out how to get it to stay/save the way I sorted or filtered.  
     
    I appreciate any help you can offer.
     
    Thank you again,
     
    Bryce
  • Profile Image

    Answered by bdabbs on January 06, 2017 at 09:45 AM
    Hi Mike, thank you for responding. The "=importrange()" I used is 1:1000.
    I expect at least 6,000 submissions in 2017. That said, I started with
    5,000 but then lowered it thinking it might solve my problem since the
    sheets start at 1,000. Obviously it did not help with my sorting/filtering
    issue.
    My formula is currently set at =IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxx",
    "Submissions!1:1000"). Sorry, I had to use the "xxxx" rather than the
    source key for this thread because of the data being collected.
    Employees use the form to request corporate checks. Below is a partial
    snapshot of what the sheet looks like.
    [image: Inline image 1]
    ...
  • Profile Image
    JotForm Support

    Answered by olivia on January 06, 2017 at 09:55 AM

    Hi  Bryce,

    We are sorry for the confusion.

    However, I couldn't understand your last comments. You sent the same messages to this thread. Could you please give the answer to Mike's questions above:

    "Did you import the data from the original sheet to the new sheet as a whole?........

     Can you help us fully understand what you're aiming to archive by providing us more details, please? May we know what kind of sorting and filtering are you doing and the outcome that you would like to produce?"

    In addition, screen shot does not appear in your comment. Please have a look at the following guide about how to add a screen shot to the forum: How-to-add-screenshots-images-to-questions-to-the-support-forum  

    We'll be awaiting for your reply.

    Thank you.

  • Profile Image

    Answered by bdabbs on January 06, 2017 at 11:22 AM

    Hi Olivia,

    "Did you import the data from the original sheet to the new sheet as a whole?........

    Yes, I imported the new sheet as a whole by using 1:1000 which covers all the way across and all the way down.  I also tried selecting only the columns that had data and 1000 down.  I did the 1000 down because that's the standard sheet size without adding any new rows.  I can't select only the current data showing because it's integrated, otherwise it wouldn't capture any new data when someone submits a request...right?  

    Can you help us fully understand what you're aiming to archive by providing us more details, please? 

    Employees use the form to request corporate checks.  We need to archive the who ordered it, why it was ordered, who it is for, when it was ordered, what was the check number, what was the amount, what project is it for, etc.    

    May we know what kind of sorting and filtering are you doing and the outcome that you would like to produce?"

    Yes, once the jotform is submitted and the data populates, accounting needs to sort and filter the checks and dates by project, check number and submission date.  For example, They might filter down to one (1) project and then sort by date and then want to sort the check numbers in order.  

    Unfortunately when I sort or filter, the data almost immediately reverts back to it's original format.  I can't figure out how to get it to stay/save the way I sorted or filtered.
     

    Below is a partial snapshot of what the sheet looks like.

     

     
    Anyway to fix this?
     
    Thank you again,
     
    Bryce 

     

  • Profile Image
    JotForm Support

    Answered by Mike_G on January 06, 2017 at 01:06 PM

    What you're aiming to do is possible, however, it would require you to use the "=SORT()".

    Refererence: https://support.google.com/docs/answer/3093150?hl=en

    Here's a sample:

    =sort(IMPORTRANGE("1iGjd2k9TbrLhc******************************2k9TbrLhc","Sheet1!A1:Z1000"),5, False)

    Where "importrange()" will serve as the range to be sorted. Which is from A1 to Z1000 of the original sheet.

    And "5", is the column that you will be sorting. In my example, it would be the E column, since it is the 5th column of the range.

    Then "False", indicates descending order. Where "True" is the ascending order.

    You can probably sort the imported data from a separate sheet and filter it on another.

    Reference: https://support.google.com/docs/answer/3093197?hl=en

    You would use the "importrange" as the range in both "=FILTER()" and "=SORT()".

    I hope this helps, if you have questions regarding this, please feel free to contact us again anytime.

    Thank you.

  • Profile Image

    Answered by bdabbs on January 06, 2017 at 07:27 PM

    Mike, your solution worked better than you maybe even thought it would!  I know you mentioned the possibility of a separate sheet to do the filtering.  However, I have multiple columns now sorting in real-time as data comes in and the filters now work perfectly without a formula.  Your solution is actually better than what I was hoping for when I asked the question.  Thank you for all the help and effort.  

    Take care,

    Bryce