Skip to main content

HOW TO: Populate data into Google Sheets

Comments

19 comments

  • Official comment
    Kate Larson Principal Technical Writer Community manager
    Celigo University Level 2: Seasoned

    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.

  • 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

    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

    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
  • 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
  • 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
  • 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
  • Pangaia Admin

    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 Principal Technical Writer Community manager
    Celigo University Level 2: Seasoned

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

Please sign in to leave a comment.