HOW TO: Populate data into Google Sheets
Today's post is brought to you by our very own Kelly Izer, whom many of you know and love!
Depending on what you want to do in the spreadsheet you have to set the URI accordingly. Here are the methods:
- append: Appends values to a spreadsheet.
- batchClear: Clears one or more ranges of values from a spreadsheet.
- batchClearByDataFilter: Clears one or more ranges of values from a spreadsheet.
- batchGet: Returns one or more ranges of values from a spreadsheet.
- batchGetDataByFilter: Returns one or more ranges of values that match the specified data filters.
- batchUpdate: Sets values in one or more ranges of a spreadsheet.
- batchUpdateByDataFilter: Sets values in one or more ranges of a spreadsheet.
- clear: Clears values from a spreadsheet.
- get: Returns a range of values from a spreadsheet.
- update: Sets values in a range of a spreadsheet.
In this scenario, you'll be retrieving data in a Google folder from an FTP site, then you'll do an import step to clear all data from your spreadsheet. Finally, you'll update the Google Sheet using our REST connector.
1. For your import, make sure to choose REST from Form view. Set your Relative URI to point to the Sheet:
Use the following format. If your sheet has a particular name that includes spaces, use quotes around the name, such as /"My Sheet"
/Sheet1!A1:H20:clear
2. In the next import step, you'll populate your data from Google Sheets
3. Choose the PUT method.
4. In Relative URI, specify the sheet, range, and valueInputOption
/Sheet1!A1:Z5000?valueInputOption=RAW
When populating the data, you have to build the HTTP request body to populate each line of data like this:
{
"values": [
["companyid", "CompanyName", "ContactName", "Address", "City", "PostalCode", "Country"],
{{#each data}}
["{{companyid}}", "{{CompanyName}}", "{{ContactName}}", "{{Address}}", "{{City}}", "{{PostalCode}}", "{{Country}}"]
{{#unless @last}},{{/unless}}{{/each}}
]
}
where each of the values in the expression match the headers in your spreadsheet:
Et voilà! Our next post will be from our support team's beloved Dan Claypool on how to get rows of data from Google Sheets into name/value pairs, so check back tomorrow!
If you have Google Sheets tips or any other great tips, please comment below or create your own post and let us know! Thanks and happy integrating!
-
Official comment
Hi Google Sheets integrators,
We've added a new topic to our Help Center that walks through setting up an import to Google Sheets using our universal REST API connector. -
Posted on behalf of Pangaia Admin
Hi,
Thanks for the post - it's really helpful.
I've followed the method and connected to Google Sheets. However, when I get to the stage of Build HTTP Request Body, I've copied in the code supplied but am getting an error message: Evaluated result is not valid JSON. Unexpected token [
Do you know what might be causing this?
0 -
Hi Pangaia Admin.
It looks like you might just be missing a comma after:
["companyid", "CompanyName", "ContactName", "Address", "City", "PostalCode", "Country"],
The error says "unexpected" because it was looking for something, either a } (if it was the last row) or a comma if there are more rows to come.
Thanks so much for writing in! We'd love to hear more handlebars questions you have.0 -
Hi - thanks for coming back to me super-quickly.
I tried adding in that comma but it just shifted the error further down. This is what I'm trying at the moment - I'm a bit foxed as to what to try next?
0 -
Hi Pangaia Admin,
Thanks for letting us know about this. I'll consult with our experts and try to get an answer for you. Sorry about the delay in responding!
0 -
Hi Pangaia Admin,
Our example uses a data array, while it looks like your data uses a single record. You may need to group your data, which you could do using the Key columns option on your export. Here's an example export using FTP with a CSV file to show where the field is located. You'll need to have selected your file and have checked the Multiple rows per record checkbox for it to display.
For the Key columns field, you'll likely need to edit your data to provide some sort of column to group by. You'll notice in our data, we have a field called Key: "1" in the data array. In some scenarios, that might mean grouping by transaction number so that you get all the rows for that transaction. Here, it seems that you want group all the rows together, so the easiest solution would be to just add another column to your data and put a matching value (such as the number 1) in that column for all rows. In selecting that column in the Key columns multi-select checkbox list, you're telling integrator.io to group by that column, which will group all rows together. Let us know if this doesn't resolve your issue!
0 -
That worked great - thanks Courtney
0 -
That's great to hear! Thanks so much for letting us know, Pangaia Admin!
0 -
Hi, thanks for sharing a great article.
I'm having difficulty when trying to set up an Import in Sheets. The only option I have is Lookup addition records (per record). NetSuite is the export source. Any ideas of what I'm missing?
Thanks in advance.
0 -
I found a workaround but it requires two flows. NetSuite to FTP then FTP to Sheets. If there is a better solution please let me know. Thanks again!
0 -
Hi, Matthew Cole. We're actually in the process right now of improving our Google Sheets documentation, and your question came at a great time. Sorry if I misled you.
First, a tip: there's a brand new NetSuite – Google Sheets template that shows a model integration similar to what you're looking for.
Then, to make your own GSheets import, follow these steps:
- For the import's Application type, select REST API.
- Then, for the import's Connection setting, select Google Sheets.
- Continue with the import's settings as shown above.
2 -
Hi Stephen, thanks for your response.
Great news on the updates forthcoming! Thanks for sharing the template. After adjusting the listener to export Items (instead of Customers) I receive this error: "SSS_INVALID_RECORD_TYPE: Item". I believe it was at this point on my initial pass that I switched to a scheduled export.
Do you know if it is possible to export Item record types?
Thanks again.
-Matthew
0 -
Hey Matthew, if you still want to do a Realtime trigger on Item, you would need to select the Record Type as one of the Item Sub Types (example - Inventory Part or Non-Inventory Part). That is just how the NetSuite API works.
1 -
Hi Stephen,
Could you make the template available on the eu.integrator.io subdomain please?
Thanks,Stewart
0 -
Hi Pangaia,
The Google Sheets template is now available in the eu.integrator.io marketplace.
0 -
Hey all,
Trying to setup a flow to export certain items and a custom item-level date field from NetSuite and import into a Google Sheet. About 90 total items meet this criteria. So will be about 90 rows in the sheet. I have two import steps, one to clear the sheet and the second to populate the NetSuite data. Export is working fine but the import is giving me two issues that I'm struggling with:
1. A "quota exceeded" error which is happening both on the import to clear the sheet (about 5 errors and 80 successes) and the import to populate the data (almost all erroring, but sometimes a couple successes).
"{\n \"error\": {\n \"code\": 429,\n \"message\": \"Quota exceeded for quota metric 'Write requests' and limit 'Write requests per minute per user' of service 'sheets.googleapis.com' for consumer 'project_number:959874376528'.\",\n \"status\": \"RESOURCE_EXHAUSTED\",\n \"details\": [\n {\n \"@type\": \"type.googleapis.com/google.rpc.ErrorInfo\",\n \"reason\": \"RATE_LIMIT_EXCEEDED\",\n \"domain\": \"googleapis.com\",\n \"metadata\": {\n \...
2. The 2nd import only seems to write a single row and continues to overwrite that row. So I'd end up with a Google Sheet with only two rows: the header and one row of data.
Any help here would be greatly appreciated, thank you!
0 -
Hi Justin Bodin -
I'm checking with some folks internally re: your specific challenges. In the meantime, if you haven't seen this article in our Help Center, take a look:
Import example: Add rows to a Google sheetThanks,
Kate
0 -
Justin Bodin, I encountered similar issue where same rows were getting overridden. You can check here: https://youtu.be/xQ2aXoJ1HTI
1 -
SteinChu wow, thank you! That solved my 2nd issue! The "Page Size" setting on the NetSuite export is one of the few settings that I did not test changing. Solid debugging on your part.
I'm still getting the "quota exceed" error, so hopefully I can get to the bottom of that too.
Thank again for your input!
0 -
Hi Everyone
I am also keen to get around this quota exceeded error. Google (help desk) mentioned they can't adjust the quota on their side for these type of situations. I am hoping Integrator.io can control the number of records sent (on their side) in accordance to the Google quota.
0 -
Can you try setting the 'Concurrency level' to 1 on the Google connection, and see if that helps? Setting the concurrency level to 1 instructs integrator.io to only send one single HTTP request at a time.
0 -
Hi Scott - thanks for your reply. I tried that but it still generated an error. Also there is a complication in that I have a flow that clears the sheet and another that writes a fresh set of data on the sheet (to account for edits)
0 -
Is there an easy way to set the ValueInputOption to USER_ENTERED instead of RAW just for certain columns? I'm unsure of where to change this.
I noticed that with RAW, numbers are coming into the sheet as '3. I don't want the apostrophe, but I don't want to transpose words into numbers either.
Thanks
0 -
Hii @Pangaia Admin , @Courtney Jordan , @Kate Larson ,
I found a solution-
In Celigo as we know from the documents that within a single page there will be only 20 records or less than 5Mb total records within the single page for execution. So we can import 20 record max in google Sheets.
So for that, we need to combine 20 records from each page to a single record by pre-save page hook.
function preSavePageFunction (options) {
var PaginateRecord = []
var Arraydata = []
for (var i=0; i<options.data.length; i++)
{
let obj = options.data[i]
PaginateRecord.push(obj)
}
Arraydata.push(
{
PaginateRecord
})
return {
data: Arraydata
}
}And for importing the data in google sheets we have to use handlebars like this-
{
"values": [
{{#each data.0.PaginateRecord}}["{{Id}}", "{{Name}}", "{{Phone}}", "{{Industry}}"]
{{#unless @last}},{{/unless}}{{/each}}
]
}Example - I've set a SOQL query in Salesforce connector limit of 30 records then what happened 20 record is combined in the 1st record & another 10 records was combined in the 2nd record.
Note: The most effective point is that we can use "Google sheet Import" for error reporting or data reporting.
0 -
Thank you Anuj for sharing and I want to clarify that the number of records in a single page can be set by changing the page size (see https://docs.celigo.com/hc/en-us/articles/360040957071-FAQ-How-can-I-process-large-sets-of-data-). If this field is left blank, then the platform will default to use 20 records per page.
0 -
Hii viliandy leonardo ,
Thanks for correction, yes i know but forgot to mention about default paging number that is 20.
Actually problem was about importing data in sheet. It was overriding, so found the solution which is above mention.
And yes we can set more than 20 records on each page but in size, it should be less than 5Mb.
0 -
Hello,
We have a new article that covers importing data into Google Sheets. Feel free to reach out on this thread if you have any questions or concerns.
Thanks,
Kathyana
1
Please sign in to leave a comment.
Comments
27 comments