Hello, I'm desperately trying to find a way to automatically invoice customers when one of our technicians fills out an invoicing form I built in Jotform. However, when the submission comes in from the Jotform, the input table information comes formatted like:
I have tried every which way I can think of and searched up and down the internet to try to figure out how to reformat this chunk of stubborn data into a format that I can use to create line items in Zoho and I'm out of ideas. It seems that I need to get rid of the brackets, the |, and the quotations to make these into useable CSV format to then separate them into different fields then map them into a Zoho Invoicing create invoice action. But I cannot for the life of me figure out how to do all of this. I’ve tried ChatGPT writing JSON to use in a Zapier Code action and none of it will work. I’m stuck. Please help!!
Best answer by DavidLGS
You’re lucky I’m so obsessive. 😉
Here’s the A.I. prompt to take the Jotform garbage and transform it into a valid JSON payload:
You are a master of data parsing and JSON formatting, and you are asked to process Jotform output data into a structured format for Zoho invoices.
Given the following raw input text from a Jotform:
```
<<THIS IS WHERE YOU INSERT THE DYNAMIC DATA FROM THE JOTFORM TRIGGER>>
```
Your task is to parse this input and create JSON array of line items containing the key:value pairs for a Zoho invoice. Follow these guidelines:
1. The input consists of datasets enclosed in square brackets, separated by vertical bars (|).
2. Each dataset represents a row from the form and contains four values: item name, price, quantity, and total.
3. Ignore any datasets where the first value is empty or a number, as these represent empty rows.
4. For valid datasets, extract the information and create a line item object.
5. The line item object should consist of multiple key:value pairs, which should all be accessible to add to individual fields of a Zoho invoice.
Please remove any JSON markers. i.e. '''json at the beginning and ''' at the end. I literally just need the raw payload itself.
Then you’ll add Code step with Javascript:
const lineItems = JSON.parse(inputData.raw_json); // Parse the input JSON// Process each line item to create an array of key:value pairsconst processedItems = lineItems.map(item => {
return {
item_name: item.item_name,
price: item.price,
quantity: item.quantity,
total: item.total
};
});
// Return the structured datareturn { processedItems };
Here’s the output if all is working correctly:
If Zoho invoices has line item support, then your last step is Zoho Invoices. You just map the fields accordingly. For testing, I used Google Sheets, but this is what the setup looks like:
You’ll see there’s just a single row for each field and you may be worried that all 3 names show up on a single line item in Zoho, but if Zoho has line-item support, it will automatically add each row with the corresponding data.
BUT…
If Zoho does NOT have line-item support, then you’ll need to create a Looping step after the code block. It will be “Create Loop from Line Items”.
And inside the looping step, you’ll have the Zoho invoice, where each field is mapped to the loop value for those fields.
And that’s that.
Here’s the whole Zap if Zoho invoice supports line items:
And this is what it would be if Zoho does not support line items:
What have you tried with ChatGPT and how didn’t it work?
I feel like it should be able to give you an output in a useable format in a single step, but even if not, it should easily be able to put this into “csv format” so that you can use other formatted steps later on.
Well, I say that. There’s a very good chance that I needed do something with the code that I don’t know how to, I have relatively no code experience so I merely copied and pasted from ChatGPT.
I found some code on another zapier community post about the problem with Jotform data having the ]|[, so I copied and pasted that into the zap, obviously didn’t work because I didn’t know how to alter the code to be for my input data. So I put it into ChatGPT to see if it could make the changes for me based on my input set. It tried, but when I put it back into the zap it told me input something or other was undefined? And so I started over and just asked ChatGPT to write me the code I needed to format my input set (copied and pasted the output of the Jotform submission in the zap) to be used in a zap code step to format into line item format, with examples. But every time I copied and pasted that code into the zap code step it gave me the same undefined input something or other. of course I already closed it all out in frustration after my 10th attempt at pasting the error message from the zap back into ChatGPT and asking it to make the changes and going around and around. I can go back through it if you would like, but if you have any better ideas I'm happy to try those out!!
Like I said, this very much could all be user error, I'm just now diving into the automation stuff so it’s all pretty new!! I am happy to take any advice you have!! Thank you so much for the quick reply!!
Let’s just start from the beginning. Given your data here, can you, the human, retype it in the format you are ultimately trying to achieve? That way I can see what may be needed to go from start to finish.
The first two items look like, line items in the format of “description”, “cost”, “quantity”, “total”. But then after the first two, I get lost in what those are or should be. (Maybe you already said that, but I was having problems following.)
Sorry, very stream of consciousness haha. Yes. So the data comes from a table on a Jotform that looks like this:
So the technicians choose a service from the dropdown in column 1, add the fee in column 2, quantity in column 3 and the price in column 4 is automatically calculated. The form is setup to have space for multiple services, as we may perform up to ten services at a job. So when the data comes in from JotForm it will have the rows that are filled out, such as you noticed in the above data set. And then any unused rows come in as the [“”,””,””,”0”].
So ideally what I would like is to get the above data set that comes from this table from: ["Basic Inspection Fee","200","1","200"]|["Sprinkler System","35","1","35"]|["Water Well","175","1","175"]|["","","","0"]|["","","","0"]|["","","","0"]|["","","","0"]|["","","","0"]|["","","","0"]|["","","","0"]|["4","0","5","7"]|["0","1","2","3","4","5","6","7","8","9"]
To:
1: Basic Inspection Fee
Fee: 200
Qty: 1
Price: 200
2: Sprinkler System
Fee: 35
Qty: 1
Price: 35
etc.
So that next I can map those fields into the line item fields for the Zoho Invoicing. I believe that I can set the Zoho Invoicing to ignore empty rows, so I’m not too worried about those. I just need a way to I guess get rid of the brackets and quotation marks so that I can map the data into Zoho Invoicing. I have gotten far enough to get the line items separated, but they all still have the brackets and quotes and I'm hoping that I don’t have to do extra formatter steps to remove each of those characters individually, that seems kind of silly.
I thought that was kind of the idea of the Zapier Utility line item-to-text feature, but it did not remove the brackets or the quotes when I tried to use it with several different inputs.
I hope that’s a little clearer, but please let me know if I can give you any other information to help! Thank you so much for your time!
You can use the “AI by Zapier” step to create a prompt, feed the Jotform data into it, and it will spit out the desired data.
I told it to output them as line items, but I didn’t add a next step to verify if they are, or it’s just formatted text. Here’s the AI step and its output:
Wow, amazing. I definitely tried the AI by Zapier but clearly didn’t do the prompt right because you nailed it. I will copy and paste that in directly and use it. Thank you so much for your help!!
Glad I could point you in the right direction. Like I mentioned, the prompt might have to be tweaked a little bit if the output isn’t actually line items. The more I look at it, the more I think it’s just formatted text.
Okay, I spoke too soon. So this all looks fantastic, but when I got to map the data into the Zoho Invoice, it is all returning as one block of text, instead of each item of each line item being mappable individually.
For context, this is what the Zoho Invoicing zap step is asking for and how the data is being given to me from the AI step:
Yes I see, sorry I missed that. Do you have a recommendation of how to do that? I tried a couple things with the prompt generator and then adding steps to the original one that said make them into individual mappable line items and it didn’t work (once again, super newbie, don’t know much!). If I do the formatter step to separate the text first, do I then have to run the AI step for each line item independently? That would end up being like, 18 different steps so I'd think it would be better to do it after. I tried the text to line item zapier utility and it did not return separate mappable items.
When I use what you entered above, I'm still only getting one output that is mappable into the Zoho step. I tried making it give me each line item category instead of each line item itself, and eventually with a formatter step got the test step to look like each item was individually mappable, but when I got to the Zoho step it still only gave me the option of mapping the one ouput chunk of text.
Is there a simpler way I could/should be doing this? I thought that using a Jotform would be the easiest way to get the information into the format I need it for this, but obviously I was wrong. What I really wanted to do was be able to make Square invoice directly, but that’s not an option, so I'm trying to use Zoho Invoicing and that doesn’t seem to be working out either. Im just at a loss now.
Here’s the A.I. prompt to take the Jotform garbage and transform it into a valid JSON payload:
You are a master of data parsing and JSON formatting, and you are asked to process Jotform output data into a structured format for Zoho invoices.
Given the following raw input text from a Jotform:
```
<<THIS IS WHERE YOU INSERT THE DYNAMIC DATA FROM THE JOTFORM TRIGGER>>
```
Your task is to parse this input and create JSON array of line items containing the key:value pairs for a Zoho invoice. Follow these guidelines:
1. The input consists of datasets enclosed in square brackets, separated by vertical bars (|).
2. Each dataset represents a row from the form and contains four values: item name, price, quantity, and total.
3. Ignore any datasets where the first value is empty or a number, as these represent empty rows.
4. For valid datasets, extract the information and create a line item object.
5. The line item object should consist of multiple key:value pairs, which should all be accessible to add to individual fields of a Zoho invoice.
Please remove any JSON markers. i.e. '''json at the beginning and ''' at the end. I literally just need the raw payload itself.
Then you’ll add Code step with Javascript:
const lineItems = JSON.parse(inputData.raw_json); // Parse the input JSON// Process each line item to create an array of key:value pairsconst processedItems = lineItems.map(item => {
return {
item_name: item.item_name,
price: item.price,
quantity: item.quantity,
total: item.total
};
});
// Return the structured datareturn { processedItems };
Here’s the output if all is working correctly:
If Zoho invoices has line item support, then your last step is Zoho Invoices. You just map the fields accordingly. For testing, I used Google Sheets, but this is what the setup looks like:
You’ll see there’s just a single row for each field and you may be worried that all 3 names show up on a single line item in Zoho, but if Zoho has line-item support, it will automatically add each row with the corresponding data.
BUT…
If Zoho does NOT have line-item support, then you’ll need to create a Looping step after the code block. It will be “Create Loop from Line Items”.
And inside the looping step, you’ll have the Zoho invoice, where each field is mapped to the loop value for those fields.
And that’s that.
Here’s the whole Zap if Zoho invoice supports line items:
And this is what it would be if Zoho does not support line items:
Wow! Thank you for confirming that @DavidLGS ’s resolution got the Zap running. This will significantly help our Community members to have as a reference for the same issue.
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. This information might be about you, your preferences or your device and is mostly used to make the site work as you expect it to. The information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings to find out more and change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
More about cookies
Customize which cookies to allow
Marketing cookies
These cookies may be set through our site by our advertising partners. They may be used by those companies to build a profile of your interests and show you relevant adverts on other sites. They do not store directly personal information, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less targeted advertising.
Social media cookies
label
These cookies are set by a range of social media services that we have added to the site to enable you to share our content with your friends and networks. They are capable of tracking your browser across other sites and building up a profile of your interests. This may impact the content and messages you see on other websites you visit. If you do not allow these cookies you may not be able to use or see these sharing tools.
Analytics/performance cookies
These cookies enable the website to provide enhanced functionality and personalization, and allow us to count visits and traffic sources so we can measure and improve the performance of our site. They help us to know which pages are the most and least popular and see how visitors move around the site. These cookies may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies then some or all of these services may not function properly and we will not know how you are using our site.
Functional cookies
label
These cookies enable the website to provide enhanced functionality and personalisation. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies then some or all of these services may not function properly.
Essential cookies
Always active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work. These cookies do not store any personally identifiable information.