Welcome to the Onshape forum! Ask questions and join in the discussions about everything Onshape.

First time visiting? Here are some places to start:
  1. Looking for a certain topic? Check out the categories filter or use Search (upper right).
  2. Need support? Ask a question to our Community Support category.
  3. Please submit support tickets for bugs but you can request improvements in the Product Feedback category.
  4. Be respectful, on topic and if you see a problem, Flag it.

If you would like to contact our Community Manager personally, feel free to send a private message or an email.

Is there a way to paste some data into a generic table drawing other than one cell at a time?

eric_pestyeric_pesty Member Posts: 1,461 PRO
I have a table of data generate in excel (30 rows x 5 columns) and I want to put this on a drawing, however I can't seem to find a way to paste data across multiple cells...

I can paste a bunch of data into one cell of the drawing table but that's not helpful... I refuse to believe it can't be done, am I missing something obvious?

I really don't want to copy/paste 150 times!
Tagged:

Comments

  • shawn_crockershawn_crocker Member, OS Professional Posts: 798 PRO


    Haha!  I also can't seem to find a way to do this?
  • eric_pestyeric_pesty Member Posts: 1,461 PRO
    @shawn_crocker
    Thanks for trying! I got confirmation it's not currently possible, which is pretty crappy and disappointing...
    My workaround was to paste each column into its own cell so I only had to paste 5 times. At least that way it's still text, it's just one row...

  • shawn_crockershawn_crocker Member, OS Professional Posts: 798 PRO
    I have needed this from time to time also.  I get "agile" and think it is a great development process but sometimes a need seems just too obvious to leave out.
  • eric_pestyeric_pesty Member Posts: 1,461 PRO
    Yeah it's interesting how every CAD program seems to re-invent tables seemingly "from scratch"!

    You really would expect that if you select data from a table and paste into another table structure it would figure out what you are trying to do. Especially since copy pasting in Onshape tables (mostly) works for config/BOM tables!
  • bryan_lagrangebryan_lagrange Member, User Group Leader Posts: 792 ✭✭✭✭✭
    Bryan Lagrange
    Twitter: @BryanLAGdesign

  • eric_pestyeric_pesty Member Posts: 1,461 PRO
    @bryan_lagrange
    Interesting, I might have to check that out, although it seems like a very convoluted way to get to what I want...

    It seems like a fundamental design limitation in the drawing tables: you can select multiple cells to apply formats etc... But you can't affect the data in a cell without first "entering" that cell. Even selecting a cell and hitting the delete key doesn't affect the data like you would expect so it's no wonder you can't paste across multiple cells.
  • bryan_lagrangebryan_lagrange Member, User Group Leader Posts: 792 ✭✭✭✭✭
    I was able to connect to google sheets and have that placed in a drawing. When google sheets is connected to Onshape it puts a .json file in a tab. In drawings go to insert bom table and select bom data to insert the .json file onto a drawing sheet.


    Bryan Lagrange
    Twitter: @BryanLAGdesign

  • eric_pestyeric_pesty Member Posts: 1,461 PRO
    edited August 2022
    Hum,
    Seeing you could import a JSON I was hoping to circumvent the google sheet part (it's not really part of my workflow here). It's easy enough to convert data to a JSON table so I tried that but I got an "unable to retrieve BOM data" error.

    Part of the issue might be that it's intended to be a BOM rather than just a generic table... I'll have to dig into a it a bit more to see if I can spoof it...

    EDIT:
    @bryan_lagrange
    Can you paste the raw JSON code it spat out in here (should just be plain text)? It looks like there are a number of ways to describe tables in JSON and it might be expecting a specific one...

    Thanks,
  • bryan_lagrangebryan_lagrange Member, User Group Leader Posts: 792 ✭✭✭✭✭
    Here you go:

    {"bomTable":{"formatVersion":"1.0","id":"not used","source":"https://docs.google.com/spreadsheets/d/13MzjsA5FAXxqrKTjxME1WSvdBrMi72RzxdeHBGg8iUw/edit","name":"Not implemented","partNumber":"Not implemented","createdAt":"Not implemented","bomSource":{"document":{"documentId":"Not implemented","documentName":"Not implemented"},"workspace":{"id":"Not implemented","name":"Not implemented","description":"Not implemented"},"element":{"id":"Not implemented","type":"Not implemented","name":"Not implemented","description":"Not implemented","partNumber":"Not implemented","revision":"Not implemented","state":"Not implemented"}},"items":[],"headers":[{"name":"test","propertyName":"test","order":1},{"name":"test","propertyName":"test","order":2},{"name":"test","propertyName":"test","order":3}]}}

    Bryan Lagrange
    Twitter: @BryanLAGdesign

  • eric_pestyeric_pesty Member Posts: 1,461 PRO
    @bryan_lagrange
    Thanks, that looks like a lot of extra "baggage" just to display 3 cells!

    Did a quick attempt at "hacking" it but didn't work... I'm going to do an IR to allow inserting a "generic table" in a drawing (from a JSON... or CSV)


  • Alex_PittAlex_Pitt Member Posts: 46 PRO
    @eric_pesty

    Hi, this was bugging me too, as a lot of my BOMs are in refined/processed in excel.

    So I dug in a bit today and wrote some VBA code to create a "Save as JSON" excel button that works for Onshape.



    to this...




    WARNING / DISCLAIMER: This is a total Hack. I have very little coding experience and I've not extensively tested it yet, but it seems to work for at least this one simple example.  I don't really have time to support or refine this code but hopefully it will be useful to some.

    Thanks go to the following tutorials

    The meat of it is based on this...
    Private Sub SaveAsJSONbutton_Click()
    'my addition
    Const startBlurb2Replace As String = "{~bomTable~:{~formatVersion~:~1.0~,~id~:~not used~,~source~:~url removed~,~name~:~Not implemented~,~partNumber~:~Not implemented~,~createdAt~:~Not implemented~,~bomSource~:{~document~:{~documentId~:~Not implemented~,~documentName~:~Not implemented~},~workspace~:{~id~:~Not implemented~,~name~:~Not implemented~,~description~:~Not implemented~},~element~:{~id~:~Not implemented~,~type~:~Not implemented~,~name~:~Not implemented~,~description~:~Not implemented~,~partNumber~:~Not implemented~,~revision~:~Not implemented~,~state~:~Not implemented~}},~items~:"
    startBlurb = Replace(startBlurb2Replace, Chr(126), Chr(34))
    'MsgBox startBlurb

    Dim ySheet As Long
    Dim xSheet As Long
    Dim yData As Long
    Dim xData As Long

    ySheet = Rows.Count
    xSheet = Columns.Count


    'this finds last row# of Data (yData) with Error handling for a blank sheet

    On Error Resume Next
    yData = Cells.Find(What:="*", _
    After:=Range("A1"), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    On Error GoTo 0

    'this finds last column# of Data (xData) with Error handling for a blank sheet

    On Error Resume Next
    xData = Cells.Find(What:="*", _
    After:=Range("A1"), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Column
    On Error GoTo 0


    Dim headers As String
    Dim finalHeaders As String

    'The following collects values of first row and writes to headers
    iRow = 1
    For iCol = 1 To xData
    If iCol = 1 Then
    headers = ",~headers~:[{~name~:~" & Cells(iRow, iCol).Text & "~,~propertyName~:~" & Cells(iRow, iCol).Text & "~,~order~:" & iCol & "}"
    Else
    headers = headers & ",{~name~:~" & Cells(iRow, iCol).Text & "~,~propertyName~:~" & Cells(iRow, iCol).Text & "~,~order~:" & iCol & "}"
    End If
    Next iCol
    headers = headers & "]}}"

    finalHeaders = Replace(headers, Chr(126), Chr(34))
    'MsgBox finalHeaders


    'below from JSON export tutorial

    Set objectProperties = CreateObject("Scripting.Dictionary")

    For Each C In ActiveSheet.ListObjects(1).HeaderRowRange.Cells
        objectProperties.Add C.Column, C.Value
    Next

    Dim collectionToJson As New Collection

    For Each r In ActiveSheet.ListObjects(1).ListRows
        Set jsonObject = CreateObject("Scripting.Dictionary")
        For Each C In r.Range.Cells
            jsonObject.Add objectProperties(C.Column), C.Value
        Next
        collectionToJson.Add jsonObject
    Next

    fileSaveName = Application.GetSaveAsFilename(fileFilter:="JSON Files (*.json), *.json")

    If fileSaveName <> False Then
    fileNumber = FreeFile
    Open fileSaveName For Output As fileNumber
        Print #fileNumber, startBlurb & JsonConverter.ConvertToJson(collectionToJson) & finalHeaders
      
    End If

    End Sub

Sign In or Register to comment.