Advanced Paypal Pro Customization - Google Spreadsheet ImportRange

  • headshotretouching
    Asked on April 27, 2014 at 1:03 PM

    Hi,

    I am struggling to figure out the best way to consolidate different services I offer into one form with the end goal being that the integration with google spreadsheets for all orders is integrated into one google spreadsheet. Currently, I have 3 forms for 3 service options, and therefore have 3 google spreadsheets to deal with, which is too overwhelming.

    The reason I currently have 3 forms instead of 1 is because I can only have 1 Paypal pro payment tool per form, and since each service has a specific set of sub-options for users to select, I'm not sure how to consolidate them. I only work with PayPal, so I cannot have different types of payment tools in my form. I'm hoping that the experts at Jotform might be able to provide some sort of coding on their end to make this possible.

    I offer 3 service options (currently in 3 different forms): Express, Premium and Custom.

    Express is $20 with 3 optional services included, in the Paypal Pro payment tool $20 Express Service is mandatorily selected and the three service options are auto-selected, with the user able to deselect.

    Premium is $25 with 3 additional service optional services included (six total including the same ones offered from Express), in the Paypal Pro payment tool $25 Premium Service is mandatorily selected and the six service options are auto-selected, with the user able to deselect.

    Custom is price dependent on the service options chosen, starting at $25 when no additional services are selected from the twenty or so optoins. In the Paypal Pro payment tool $25 Custom Service Base Price is mandatorily selected, the same 6 included services from Premium are autoselected with user able to deselect, and the rest of the options are listed for the user to select as desired, at an additional cost of $5 per additional service selected. And as a condition, I have designed the form so that if a user enters text in a box asking if they have any requests not listed as an option, it hides the payment tool and submission button and replaces it with a jotform purchase order tool and "request quote" submission botton so that we can then send them a custom quote on our own.

    I would love it if there is a way to either have submissions for all forms integrated with just 1 google spreadsheet OR consolidating all forms into 1, without losing the service-specific pricing and auto-selection settings for each. And with either method there being a service selection item in the form (Express, Premium or Custom, visible if all in one form, hidden if 3 forms integrating into one spreadsheet) to appear as a column in the spreadsheet.

    And lastly, I will soon be adding a second brand for the services. I assume it will be easiest to use the same exact forms so the information is integrated also on the same google spreadsheet, since the services are identical. And so, is there a way to have a hidden field in the form that can auto-select the brand based on the pop-up link the user clicked on, so that we can easily see on the google spreadsheet which brand the user is coming from?

    Thanks so much!

  • Cesar
    Replied on April 27, 2014 at 2:51 PM

    In regards to your question on combining all data into a single file. Kindly take a look at the following app: http://apps.jotform.com/app/combine_submissions

     

    Is there a way to have a hidden field in the form that can auto-select the brand based on the pop-up link the user clicked on, so that we can easily see on the google spreadsheet which brand the user is coming from?

    There are different ways you can go about this. You can set a hidden field on the form with a hidden value depending where the form is embedded this way you can identify where the uer is coming from.

    You can also use the widget: http://widgets.jotform.com/widget/get_referrer which extracts the URL the user came from and adds it to the submission data. Do let us know if you need further assistance. Thank you.

  • headshotretouching
    Replied on April 27, 2014 at 3:13 PM

    I see the combining app you suggested creates a csv file. For my workflow, I will be exclisively be using a google spreadsheet to track submissions, as it automatically updates in real-time for my colleagues to view. Is there a way to combine form data into the google spreadsheet integration?

    Same with the ablity to know which brand the user is submitting from -- also needs to be including in the google spreadsheet.

    Thank you!

  • Elton Support Team Lead
    Replied on April 27, 2014 at 6:13 PM

    @headshotretouching 

    Unfortunately, that's not possible with the current version of Google Integration. What you can do for now is to use IMPORTRANGE feature on your Google Spreadsheet as a workaround. This allows you to import data from a spreadsheet to another.

    Check this guide as your reference.

    https://support.google.com/drive/answer/3093340?hl=en

    Example:

    Formula

    IMPORTRANGE("0AgxqsUM6gnFjdDQ2OGV2U0ZUbTI3S3FfUEdfVWpnaFE", "Submissions!A1:C10")

    0AgxqsUM6gnFjdDQ2OGV2U0ZUbTI3S3FfUEdfVWpnaFE - is the spreadsheet key taken from the source spreadsheet URL after key=, example:

    https://docs.google.com/spreadsheet/ccc?key=0AgxqsUM6gnFjdDQ2OGV2U0ZUbTI3S3FfUEdfVWpnaFE&usp=drive_web

    Here's a video tutorial:

    If you have further questions, feel free to let us know. Thanks!

  • headshotretouching
    Replied on April 27, 2014 at 6:45 PM

    Thank you! At least with this workaround I can have each form as a tab on one google spreadsheet, which will certainly make it less overwhelming.

  • headshotretouching
    Replied on May 26, 2014 at 12:56 PM

    @EltonCris

    I think I've found a way for multiple forms to integrate into a single google spreadsheet and a single dropbox folder.

    Would you be able to run tests on it to make sure it won't fail?

    I cloned a form with existing integrations to Dropbox and Google Spreadsheets, which were passed on to the new, cloned form. When I made a submission on the new form, the google spreadsheet's previous data was immediately erased and replaced with only the new submission, but future submissions on both the original and clone forms are appearing on the spreadsheet.

    So, this is where I want to be cautious until it's fully tested: will the previous data only be erased when a new form receives its first submission? Meaning that once I perform new submissions on all cloned forms, the data will remain intact moving forward? Or are there any other conditions which will erase previous data, such as updating a form, etc?

    The dropbox folder did not have the "reset" issue, and contains all previous submissions, and all new submisssions from both the original and cloned forms.

    Thanks!

  • jedcadorna
    Replied on May 26, 2014 at 1:42 PM

    Hello,

    If I'm not mistaken I can remember that there was a ticket raised to our level 2 about the integration being included to the new cloned form. So I guess this might be fixed but I'm not sure when.

    So basically what I mean is that this is something that is not really included as a feature, but something that can be improved sooner or later. From what I know the integration only works one way, my colleague also stated that its not currently possible, you might be able to clone the integration but you might experience a different behavior as this function is not really part of the feature.

    We'll test this more then we'll update you about our findings.

  • headshotretouching
    Replied on May 26, 2014 at 4:16 PM

    Since I really need to determine this ASAP, I've started doing some testing on my end as well. I've found that if I add a new field to the clone form, upon a new submission the google spreadsheet behaves as follows:

    - the new submission from the clone appears

    - the previous submissions form the clone remain

    - the previous submissions for the original (non-clone) disappear

    That is a problem since I need to be able to add fields to the forms, and if doing so erases the data on the spreadsheet from previous submissions from other forms, that is a major problem.

  • Mike_T Jotform Support
    Replied on May 26, 2014 at 5:17 PM

    We do not support integration of multiple forms to a single Google Spreadsheet at this time, so that a work around with cloned form is not something that we recommend to use.

    I think that we can add a ticket about the Google Spreadsheet integration to be able to map multiple forms into different worksheets of the single spreadsheet. Is this something that might work in your case?

    You might also consider trying a Jotform -> Zapier -> Google Spreadsheet integration, but it needs to be tested, and Zapier to Google Spreadsheet integration has some limits too.

  • headshotretouching
    Replied on May 26, 2014 at 5:27 PM

    If your development team can make multiple forms integration into one spreadsheet worksheet that would be fantastic.

    Multiple forms into different worksheets of a single spreadsheet would not be helpful for my needs. Although it would be easier than using google's IMPORTRANGE, as is the current workaround, it does not address my issue of having to look through many worksheets to track new orders that have been placed. Having orders placed from multiple forms into one order tracking worksheet is the solution.

    Unfortunately, I was not satisfied with Zapier's limitations and cost.

    Thanks!

  • jonathan
    Replied on May 26, 2014 at 6:21 PM

    Thank you for providing us more information about this.

    I will submit a separate post for feature request ticket about the capability to integrate multiple forms to single google spreadsheet 

    We cannot provide you any time frame yet, as to when this feature will be available. But we will surely notify you for any status update about it when available.

    Thank you.