Skip to main content

HOW TO: Populate data into Google Sheets

Comments

23 comments

  • Official comment
    Kate Larson Community moderator Principal Technical Writer
    Celigo University Level 3: Master

    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.

  • Stephen Brandt Documentation Manager Community moderator
    Engaged
    Great Answer
    Top Contributor
    Celigo University Level 3: Master

    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:

    1. For the import's Application type, select REST API.
    2. Then, for the import's Connection setting, select Google Sheets
    3. Continue with the import's settings as shown above.
    2
  • Product Management

    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
  • SteinChu Strategic Support Lead

    Justin Bodin, I encountered similar issue where same rows were getting overridden. You can check here: https://youtu.be/xQ2aXoJ1HTI

    1
  • Courtney Jordan Experience Strategy & Design Director Community moderator
    Celigo University Level 4: Legendary
    Awesome Follow-up
    Top Contributor

    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
  • Courtney Jordan Experience Strategy & Design Director Community moderator
    Celigo University Level 4: Legendary
    Awesome Follow-up
    Top Contributor

    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
  • Pangaia Admin
    Engaged
    Celigo University Level 1: Skilled

    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
  • Courtney Jordan Experience Strategy & Design Director Community moderator
    Celigo University Level 4: Legendary
    Awesome Follow-up
    Top Contributor

    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
  • Courtney Jordan Experience Strategy & Design Director Community moderator
    Celigo University Level 4: Legendary
    Awesome Follow-up
    Top Contributor

    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
  • Pangaia Admin
    Engaged
    Celigo University Level 1: Skilled

    That worked great - thanks Courtney

    0
  • Courtney Jordan Experience Strategy & Design Director Community moderator
    Celigo University Level 4: Legendary
    Awesome Follow-up
    Top Contributor

    That's great to hear! Thanks so much for letting us know, Pangaia Admin!

    0
  • Matthew Cole

    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
  • Matthew Cole

    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
  • Matthew Cole

    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
  • Pangaia Admin
    Engaged
    Celigo University Level 1: Skilled

    Hi Stephen,

    Could you make the template available on the eu.integrator.io subdomain please?

    Thanks,

    Stewart

    0
  • Rohit Gupta Product Management Director

    Hi Pangaia,

    The Google Sheets template is now available in the eu.integrator.io marketplace.

    0
  • Justin Bodin
    Great Answer
    Celigo University Level 2: Seasoned
    Engaged

    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
  • Kate Larson Community moderator Principal Technical Writer
    Celigo University Level 3: Master

    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 sheet

    Thanks,

    Kate

    0
  • Justin Bodin
    Great Answer
    Celigo University Level 2: Seasoned
    Engaged

    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
  • David Nehme
    Engaged

    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
  • Scott Henderson CTO
    Answer Pro
    Top Contributor

    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
  • David Nehme
    Engaged

    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
  • Ted Parsons

    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

Please sign in to leave a comment.