Better way of maintaining super big alg sheets

abunickabhi

abunickabhi

Legend
Joined
Jan 9, 2014
Messages
14,643
Location
Yo
WCA ID
2013GHOD01
YouTube
Visit Channel
I have been using Google sheets to maintain the algset for blindsolving.

Upto 100k cases, I have not encountered any errors, but now I am starting to feel the limitations of Google sheets and need a better way of maintaining the algs.

These are the few problems I encountered with Google sheet, and had to find workarounds for it:
https://webapps.stackexchange.com/q...sert-more-than-10-000-images-in-google-sheets
https://webapps.stackexchange.com/questions/149261/exporting-huge-google-sheet-to-a-pdf

Is there any template made in SQL, MongoDB or any other offline utility to make inverses and mirrors appear automatically for that cases in the sheet, also have alg.cubing (cubing.js), cube diagram printed out next to the case, and since it is offline, not have any limitation on the size of the sheet?

Thanks for your help! I know a bit of Python, but my knowledge is limited when it comes to databases and js.
 
You're right that spreadsheets are not the best way to go for very large amounts of data. However google sheets is still the best if you plan on doing collab work.

Maybe a solution that can scale is to use LaTeX and make sections. After all, it can handle entire book's worth of content, and you can generate TeX itself using macros or with another script. So perhaps you have a database (I suggest SQL), a python script that reads the database to make inverses and images (can generate programmatically with https://github.com/Cride5/visualcube or I can try to hack together a TikZ method) and produces the TeX code, the LaTeX takes care of generating the monster PDF. However LaTeX itself is annoying and I won't ld only do this if you really want a nice looking PDF output.
 
qwr said:
You're right that spreadsheets are not the best way to go for very large amounts of data. However google sheets is still the best if you plan on doing collab work.

Maybe a solution that can scale is to use LaTeX and make sections. After all, it can handle entire book's worth of content, and you can generate TeX itself using macros or with another script. So perhaps you have a database (I suggest SQL), a python script that reads the database to make inverses and images (can generate programmatically with https://github.com/Cride5/visualcube or I can try to hack together a TikZ method) and produces the TeX code, the LaTeX takes care of generating the monster PDF. However LaTeX itself is annoying and I won't ld only do this if you really want a nice looking PDF output.
Click to expand...
Thanks for the suggestions, I have used Latex to make my Masters thesis.
The idea of using sections to make the algs has never crossed me before, I might give it a try.
The worst case scenario is that I have all the tables imported from Google sheet, and Latex hangs up on my 16GB RAM Ryzen processor machine, while it is printing PDF. Also I do know beautiful images can be generated in Latex, no idea on how to import the cubing.js image renders into Latex code.

Can you think of any other less troublesome solution? Is mongoDB viable and a good candidate in this case?

Having a nice looking PDF output is not my priority. I already exported the google sheet to a PDF, and it is 4600 page, full of 5-style and letter quads. Also I do have smaller version of the sheet public and sharable on google, so sharing the most recent copy of the sheet is also not my priority.

My priorities are:
1) Including alg.cubing like images in the database, which reads target stickers and generates an image. I pinged @Lucas Garron about generating images with just target stickers as input. eg. UF-UB-RD-RU-LB as input should give me
bsgk.png
as output.

2) Mirrors and inverses of algs automatically entered. Using it in google sheet, slows down the sheet a lot, so currently I enter in the mirrors and inverses myself manually. I use tools to generate batches of mirrors and inverses, but entering them corresponding to the letter quad is a manual task. I used @Roman Alg inverse tool 2 years back, and currently I use algtrans.html for mirrors and inverses.
 
The exact database kind is irrelevant in this case. Your application is not really the kinds of problems databases are designed for and in fact a simple CSV or JSON document probably suffices.
I always thought of LaTeX being memory efficient to the point of being slower - I've never heard of LaTeX ever running out of memory.

IDK your requirements for the diagram: can you write a script that reads target stickers and generates a diagram for visualcube?
 
The problem with visualcube images is that they are not good for representing BLD cases. In BLD the side and the back stickers also need to be visible. I found alg.cubing images to be better representation, as only BL,BD and DB edge stickers are not visible.
 
You could create the spreadsheets with Excel VBA to actually draw a cube image for you next to the algorithm(s) for it. For example, the same that you did on page 2 of this document. (You can make a column with the algorithms and have consecutive columns next to that column all be solely for the cube image. (Because you can make the dimensions of cells have an equal length and width, thereby functioning as pixels. I'm not sure how many pixels are required to represent a case -- maybe if I could see a link to your Google sheets, that may be helpful to get an idea -- but even if you end up needing 30 rows x 30 columns to create a case image, you can always zoom out to make the images small in view and enlarge the font size of the algorithms in the algorithms column (and merge those cells as one -- all of which can be done automatically with Excel VBA).)

But this idea would only work if you don't have arrows or anything else besides a colored pixels -- which can also of course be textures too, especially for pieces to ignore (instead of using a dark gray).

And I know that a worksheet in Excel can only hold 1,048,576 rows (and, although not relevant, 16,384 columns), you can store cases for different subsets in a separate worksheet tab or in an entirely different worksheet (if Google sheets doesn't work like Excel spreadsheets . . . I don't know much about Google sheets TBH).

So the advantage of this is the file size is much smaller if colored cells are used in place of images.

(Again, if you could provide a link to one of your Google sheets, I can maybe get a better idea.)
 
Last edited:
Christopher Mowla said:
I'm surprised at your comment. Not to mention rude, but you are in favor of scripts. VBA is scripts and has a lot of capabilities. There's also python in Excel too if that's more appealing.
Click to expand...

You are seriously suggesting using spreadsheet cells as pixels for images? Even if you don't bump up into excel's hard limits (it's not designed for this quantity of data), you may bring the program to a standstill. If you are willing to put in the energy to hack together a VBA method, why not just make a proper TikZ method that is so much more flexible and robust. It is moot anyway because I believe at this scale a spreadsheet is not the right answer - a proper document preparation system like TeX is much more appropriate.

I am not even sure the colored cells would save space, given that excel's openoffice format as XML is not particularly space efficient. There exist tools to minify PNG files so a cube diagram could even be under one kilobyte for 3D and a few hundred bytes for 2D.
 
Again, I don't know exactly how his current sheet looks to be certain, but what he was asking for does not sound like a PDF printout. I am a huge fan of LaTeX myself. I wrote my 1,100 page textbook in it! And I even programmed a script to convert Microsoft Excel formulas -- of any complexity -- into parsable LaTeX code with VBA for the purpose of generating Engineering reports. (Here's the PDF documentation. If anyone is interested to actually view it, save to your machine and view with your PDF reader instead of in Google Docs to make the internal hyperlinks work.)

But the question is how is navigation for a specific case going to work in a PDF versus a database or a glorified (programmed) spreadsheet. For example, when I made my F3L PDF for K4, on page 2 is a diagram of the pieces that I numbered so that one can use the search in their PDF viewer to search for "8-7" (without quotes) to quickly find a case that has those two pieces need to be inserted (in their respective slots). But that is painful to do for a large quantity of cases! You can use internal hyperlinks and the table of contents in the LaTeX PDF, but it's still not efficient to actually use. When need a search engine, which is possible with VBA (that's my search engine which mimics Google's, with autocomplete . . . in both directions . . . it can search a 2400+ page BIBLE in a manner of seconds . . . referring back to your example with spreadsheets failing to process large amounts of data as is in a book like LaTeX can).

Everything he's looking for (automatically create mirrors, inverses, etc.) can be easily done in a spreadsheet. And best of all, it can even be that the images can be dynamically created as the viewer is viewing the alg associated with the algorithms (rows) in the current view (in between the four corner cells visible in the current screen). So that absolutely no space is needed to store the images. (VBA can be used to do conditional formatting too.)

Any of the one to four versions (mirror, inverse, etc.) of a case need to be inputted in a given row. The other (three?) will be automatically calculated in the cells. And it can be that once there is some text in a cell, that Excel will not keep regenerating the move sequences. (It will only generate sequences in which all four cells in a row are not filled in . . . and it's easy to use Excel table filters to quickly find "incomplete rows" to fill. (Much much faster than a typical For loop.) So I'm sure he will run the code to generate the formulas. He can disable that code from running once he has it run once to generate all algorithms (so that it doesn't keep searching for "incomplete rows"). Then the user will use the search engine to pull up algorithms to cases that they describe some how with the search engine. Then as those rows come up, the images for those cases will be generated simultaneously as their corresponding algorithms come into view.

The main problem is people will have to have Microsoft Office installed to use this.

But if someone has a better idea, that's fine with me. But I don't like the idea of him having to depend on Lucas Garron (or anyone else's) software for the long-term. (alg.garron.us was taken down and replaced with alg.cubing.net, which completely wrecked years of posts which linked to it. So I don't think a big project like this should be "at the mercy" of anyone else's server. He's got to think long-term, so that he doesn't have to keep "adapting" his work to changing technology as time progresses.)

abunickabhi said:
1) Including alg.cubing like images in the database, which reads target stickers and generates an image. I pinged @Lucas Garron about generating images with just target stickers as input. eg. UF-UB-RD-RU-LB as input should give me View attachment 14853 as output.
Click to expand...
I'm not sure if you ever thought about flat 3D images similar to visual cubes LL view like these, for example, but that's also an option. (And are more feasible to generate with cell pixel representations with my idea.)

And @qwr , I'm not "insisting" that this be the way to go (I saw that's what you wrote originally but reasonably changed it to "suggesting"), but I can't ignore the fact that he would have still been using Google Sheets if the data wasn't as large as it is now. This would be a way for him to continue to use his originally chosen form of medium.
 
Last edited:
I agree that a PDF document is probably not the right medium for the sheer number of cases, and something like a specialized alg database would be better. For some reason I thought the OP asked for a PDF, like the cube root 1LLL PDF, but I see now that it is asking for a way to organize. The main issue is that I don't know how the BLD algs are organized; the 1LLL PDF is the only project I know of to accumulate many algorithms and it is only 85 pages. (1LLL is already considered not feasible currently so idk how compiling 4000 pages pages is even readable by humans, but then again I am not a BLD solver and my personal philosophy would limit myself to at most one or two sheets of paper for algs.)

@abunickabhi maybe you can ask Gil Zussman about adding bld algs to his speedcubedb.com site; Gil has recently added a megaminx algs section in consultation with some mega solvers. I've been pushing for him to make it open source and to post here so that people can fork and send in PRs, but he hasn't gotten around to doing so yet.

Anyhow I am not a fan of any solution that requires using Microsoft Office / VBA (StackOverflow's most "dreaded" language according to their latest survey of programmers). I believe it is especially not designed for the use cases of an actual database with search (just because you can, doesn't mean you should), such as SQLite or MySQL. My personal experience with VBA is very limited but gave me a bad impression (not to mention whenever I hear about it nowadays it's always in the context of malware and vulnerabilities).
 
Last edited:
qwr said:
I have never seen a situation for which VBA was ever the right answer lmao
Click to expand...

VBA Code
Code: 
Sub colls()
Dim cell As Variant
Dim i As Integer
i = 1
For Each cell In Range("A1:A41")
PicPath = "http://cube.crider.co.uk/visualcube.php?stage=coll&fmt=png&size=100&view=plan&case=" & cell.Value & ""
With ActiveSheet.Pictures.Insert(PicPath)
    With .ShapeRange
        .LockAspectRatio = msoTrue
        .Width = 50
        .Height = 50
    End With
    .Left = ActiveSheet.Cells(i, 5).Left
    .Top = ActiveSheet.Cells(i, 5).Top
    .Placement = 1
    .PrintObject = True
End With
i = i + 5
Next cell    
End Sub

Sub wvolls()
Dim cell As Variant
Dim i As Integer
i = 18
For Each cell In Range("A1:A90")
PicPath = "http://cube.crider.co.uk/visualcube.php?stage=pll&fmt=png&size=100&view=plan&case=" & cell.Value & ""
With ActiveSheet.Pictures.Insert(PicPath)
    With .ShapeRange
        .LockAspectRatio = msoTrue
        .Width = 50
        .Height = 50
    End With
    .Left = ActiveSheet.Cells(i, 5).Left
    .Top = ActiveSheet.Cells(i, 5).Top
    .Placement = 1
    .PrintObject = True
End With
i = i + 3
Next cell
End Sub
 
Inserting images is rather slow compared to drawing cube images in Excel, but here's alternate code to do the same thing (with a few additional features for aesthetics):

EDIT:
I used @Roman's online Mosaic tool to effortlessly produce these sketches for the 3x3x3 and 4x4x4, for example. So it's not too tedious to actually draw the cube images with Excel with an aid such as that. Of course the images need to be touched up some before the cell addresses are made into sticker colors. The hardest part (which is doable) is to implement a Rubik's cube move parser. And you can dynamically show ONE image at a time, for the currently selected algorithm cell. That way, you can make a very high resolution image and not require more than one row per algorithm.) And I'm not insisting or suggesting this be done, but this is a way to show how it can be done and why it would be advantageous.



But anyway, continuing on . . .

To run the code,
  1. Open Microsoft Excel
  2. Navigate to: File -> Open -> Trust Center -> Trust Center Settings -> Macro Settings
  3. Dot in Enable all macros (not recommended; potentially dangerous code can run)
  4. Click OK twice
  5. Press [alt] + [F11] (to open the VB editor window (VBE))
  6. Press [ctrl] + [R] (to show the left pane, if it isn't showing already)
  7. Right click anywhere in the white beneath ThisWorkbook
  8. Navigate to Insert -> Module
  9. Copy this code and paste it.
  10. Click in a NEW blank sheet
  11. With the VBE still open, left click anywhere inside of Sub Test_colls_v2()
  12. Press [F5] (or click the play arrow button in the toolbar) to run.
  13. And . . . when you go to Save As, be sure to save as .xlsm, not as .xlsx.
(And these instructions apply to running Alexander's code as well.)

(And note that commented lines in VBA begin with a ')

Code: 
Option Explicit

Sub Test_colls_v2()

'Manually input algorithms into these cells.
Range("D1").Value = "R2 B2 F"
Range("B3").Value = "R2"
Range("B4").Value = "U2 F2"
Range("B5").Value = "D' R"

'Call the sub.
Call colls_v2(ActiveWorkbook, ActiveSheet.Name, "D1,B3:B5")

End Sub
Sub colls_v2(book As Workbook, sheetName As String, rangeAddress As String)

Application.ScreenUpdating = False

'url parameters:
    Dim startOfEveryURL As String
    startOfEveryURL = "http://cube.crider.co.uk/visualcube.php?"

    Dim stage As String
    stage = "&stage=pll"

    Dim picFormat As String
    picFormat = "&fmt=png"

    Dim imageSize As String
    imageSize = "&size=100"

    Dim cubeSize As String
    cubeSize = "&pzl=3"

    Dim view As String

    Dim alg As String
    Dim url As String


Dim rng As Range
Set rng = book.Sheets(sheetName).Range(rangeAddress)

'For each cell in the range,
    Dim cell As Range
    For Each cell In rng
    
        'Change size of cells in the range where images are to be inserted.
        With cell
            .RowHeight = 80
            .ColumnWidth = 15
        End With
    
        alg = cell.Value
    
        'Change the background color and font color of cells to the immediate LEFT of the cells where images are to be inserted.
        With cell.Previous
            .ColumnWidth = 35
            .Interior.Color = RGB(225, 225, 225) 'light shade of gray background color
            .Font.Color = RGB(255, 0, 0) 'red font
            .Font.Size = 16
            .Font.Name = "Times New Roman"
            .Value = alg
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlCenter
        End With
    
        'Just for fun, make even rows have FULL view and odd rows have last layer view.
        If Modulous(cell.Row, 2) = 0 Then
            view = "&view=full"
        Else
            view = "&view=plan"
        End If
            
        url = startOfEveryURL & stage & picFormat & imageSize & cubeSize & view & "&alg=" & alg
    
        'Put a medium border around both cells (for appearance)
        Call Cell_Border(book, sheetName, cell.Address)
        Call Cell_Border(book, sheetName, cell.Previous.Address)
    
        'Now insert the images
        Call URLPictureInsert(url, book, sheetName, cell.Address)
    
    Next cell

rng.ClearContents

Application.ScreenUpdating = True

MsgBox "Program Complete!", vbInformation, "VBA Macro by Christopher Mowla"

End Sub


Sub TestURLPictureInsert()

Dim url As String
url = "http://cube.crider.co.uk/visualcube.php?stage=pll&fmt=png&size=100&pzl=3&view=plan&alg=R2F2R2"

Call URLPictureInsert(url, ActiveSheet.Name, "A5")
End Sub
Sub URLPictureInsert(url As String, book As Workbook, sheetName As String, cellAddressToInsert As String)
'Code is originally from https://www.extendoffice.com/documents/excel/4212-excel-insert-image-from-url.html
'Also check out Edit #2 of https://superuser.com/questions/940861/how-can-i-display-a-url-as-an-image-in-an-excel-cell

Dim xRg As Range
Set xRg = Range(cellAddressToInsert)

Sheets(sheetName).Pictures.Insert(url).Select

Dim Pshp As Shape
Set Pshp = Selection.ShapeRange.Item(1)

With Pshp
    .LockAspectRatio = msoFalse
    .Top = xRg.Top + 0.5
    .Left = xRg.Left + 5
End With

Set Pshp = Nothing

End Sub


Sub Test__Cell_Border()
Call Cell_Border(ActiveWorkbook, ActiveSheet.Name, ActiveCell.Address)
End Sub
Sub Cell_Border(book As Workbook, sheetName As String, rangeAddress As String)
With book.Sheets(sheetName).Range(rangeAddress)
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlMedium

    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium

    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlMedium

    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
End With
End Sub


Sub TestModulous()
MsgBox Modulous(5, 2)
End Sub
Function Modulous(a As Variant, b As Variant)
Modulous = a - b * Floor(a / b)
End Function


Sub TestFloor()
'MsgBoxP Floor(3.21), Floor(-3.21)
End Sub
Function Floor(number As Variant)
    'Code from here: https://www.mrexcel.com/board/threads/floor-function-in-vba-code.218947/#post-1069232
    Floor = Int(number) - 1 * (Int(number) > number)
End Function
 
Last edited:
abunickabhi said:
The problem with visualcube images is that they are not good for representing BLD cases. In BLD the side and the back stickers also need to be visible. I found alg.cubing images to be better representation, as only BL,BD and DB edge stickers are not visible.
Click to expand...
A bit late but VisualCube allows for a transparent view which enables the side and back stickers to be visible.
You just have to set the view parameter to view=trans.
 
Cubing Forever said:
A bit late but VisualCube allows for a transparent view which enables the side and back stickers to be visible.
You just have to set the view parameter to view=trans.
Click to expand...
I tried that for a while. The images were not clear to be able to see the all the 5 edge piece swap.

Fun fact: there is transparent cube also in alg.cubing V2 which is quite similar to the graphics of transparent visualcube image.
 
abunickabhi said:
I tried that for a while. The images were not clear to be able to see the all the 5 edge piece swap.

Fun fact: there is transparent cube also in alg.cubing V2 which is quite similar to the graphics of transparent visualcube image.
Click to expand...
But you probably shouldn't be dependent on ACN for long term since in the next 7-8 years, ACN might face the same fate as alg.garron.us
 
You must log in or register to reply here.

Similar threads

abunickabhi
Better way of maintaining personal reconstructions
Replies
16
Views
3,136
Fyplus
F
Sub 20 Cuber
[Help Thread] Can someone help me be better at 3x3?(best F2L and OLL algs)
Replies
2
Views
484
Sub 20 Cuber
Sub 20 Cuber
Roux
Is VLS not just better ZB?
Replies
9
Views
968
YurikoDiaz61
Y
M
Is AI better than us at cubing
Replies
14
Views
911
SpeedCubingLord
SpeedCubingLord
ILCubing
[Help Thread] is the huameng ys3m (not huameng tg v1 or v2) better than the rs3m v5?
Replies
1
Views
524
Hiori_Fembysolver
Hiori_Fembysolver
Back
Top