Jun 032015

NPB9K9: Google Spreadsheet ‘‘especially what I need to know’™’ :http://1.JotHere.com/4706#NPB9K9

  1. O3KYPL: ‘section’
    1. ‘‘‘Pretty Link’ entry’ ‘anew’’ example /O3Bern
    2. NYM1ZP: ‘history’: ‘history in reverse order’
      1. O9GLBW: ‘op’{resume editing after {;date 2016.06.27MonPst1920 ;polish .7 ;version 1 ;words 4489 ;revision 9 after {NY0WO6 with replacement qty {99+0+358} after {tally updates {#NPB9K9 + #O4IEZZ thru #NQK6XI due in part from move + #O9D6E7 thru #NQM7ZL + #O9EY4M thru just before #NQK1ZX from changes & move out & sync loss} after repeat last ‘View the autosave’ after words 4534} after reload after date Pst1855 after {this entry, for that use #O9CNFE as template} after {need to restart laptop due to OS failing}
      2. O9GE6G: post format: update to that of {latest: /5556#O9EWSK}, so cutting
        1. NYMK00: ‘comments –here or/and elsewhere per the ‘To reply & discuss’ #O4KA53
      3. O9CUGR: post format: update to that of {latest: /5552#O9B8BZ}, so cutting
        1. NWSN5M:
          1. NUIYN7: –section’s original custom ID for ‘TOU
        2. NZ23L1: ‘completeness stage’ CONSTRUCTING
        3. O3KUE6: ‘picture’
        4. NWUAVB: remaining sections ‘definition’ + ‘post’
        5. NYM1YV: ‘definition’: concept(s) definition in NOLDef’ else in NOLDef-to-be
          1. section ‘completeness stage’ in reverse order:
            1. version 3, updating every ~1 month
          2. remaining sections
        6. ‘post’: post… –where its subsections don’t specify
          1. O3KZL1: ‘TOU’ + ‘completeness’ +‘picture’ + ‘name’ + ‘To reply & discuss’ + ‘remaining sections’: already above & in that order since these are key
          2. O3KZ2I: ‘‘‘Pretty Link’ entry’ ‘anew’’
          3. O3L134: remaining sections ‘success’ + ‘sharing’ + ‘motivation’ + ‘contributors’ + ‘annex’ + ‘history’ + ‘comments’
      4. O9CNFE: ‘op’{resume editing after {;date 2016.06.25SatPst1626 ;polish .7 ;version 1 ;words 4529 ;revision 9 after {NY0WO6 with replacement qty {57+3+361} after {updates via http://cacycle.altervista.org/wikEd-diff-tool.html {} after ‘Post restored successfully. Undo.’ after ‘The backup of this post in your browser is different from the version below. Restore the backup.’ –none of this} after {;words 4528 ;‘Post restored to revision from March 7, 2016 @ 23:20:52 [Autosave].’ after updates {#NPB9K9 thru #O3OXM6 + #O3OY1P thru #NSRZWT + #O3OXRH thru #NQMSTV + #NPBAYU thru #NPB9F6 + #NPB9F6 thru #NSVVBY move + #NYMK00} after ‘There is an autosave of this post that is more recent than the version below. View the autosave’ after words 4119} after reload after date Pst1550 after {this entry, for that use /5349#O8SBLX as template} after days after some unknown Chrome crash.
      5. O3NNO8: do NY0WO6 as seeming never has been done: replacement cnt 599~ + 7 + 344
      6. O3NA0G: format update to {latest: that of /5426#O3L134, no, /4670#O3N9JI} :O3NA0G
      7. NSVVBY: ‘{post.status.snapshot: date ’20150810Mon1323pst‘; after ID’ minutes 0~‘; revision ’8‘; words ’4310‘; version0.9‘; as ’that failed with the bizarre WP error ‘Are you sure you want to do this? Please try again.‘ which I selected which reverted the doc back to NR19NW so back 2 versions(!) but fortunately had done clipboard save so restored from that so now‘, do ’again ‘Update’‘}’
      8. NSVUWJ: ‘{post.status.snapshot: date ’20150810Mon1314pst‘; after ID’ minutes 0~‘; revision ’8‘; words ’4306‘; version0.9‘; as ’usable as is and WordPress editor stuck in ‘Saving Draft…’‘, do ’save current text in Clipboard then ‘Update’‘}’
      9. NSRVDA: ‘{post.status.snapshot: date ’20150808Sat0924pst‘; after ID’ minutes 0~‘; revision ’8‘; words ’3464‘; version0.8‘; as ’more updates to do‘, do ’‘There is an autosave of this post that is more recent than the version below. View the autosave’ getting ‘Post restored to revision from July 6, 2015 @ 15:51:16 [Autosave]’ then start putting in additions‘}’
      10. NR19NW: ‘{post.status.snapshot: date ’20150705Sun1414pst‘; after ID’ minutes 0~‘; revision ’6‘; words ’2853‘; version0.7‘; as ’more updates to do‘, do ’continue editing‘}’
      11. NR19A4: got & did ‘The backup of this post in your browser is different from the version below. Restore the backup.’; this fully restored it, and now same # of revisions (6: see next) as last, but because of hang last created an additional so
        1. NR19KU: it looks safe to just close browser as needed even w/o a save (the changes restorable as shown, and without updating what’s published) but it’s not clear yet, if under normal cases, no new revision is created.
      12. NQZK6F: ‘{post.status.snapshot: date ’20150704Sat1606pst‘; after ID’ minutes 0~‘; revision ’6‘; words ’2746‘; version0.6‘; as ’need to restart browser as is common with Windows/Chrome bugs‘, do ’nothing to see how well auto-save handles this common case ‘}’
      13. NQZJJ0: ‘{post.status.snapshot: date ’20150704Sat1552pst‘; after ID’ minutes 0~‘; revision ’5‘; words ’2683‘; version0.6‘; as ’need to restart browser as is common with Windows/Chrome bugs‘, do ’nothing to see how well auto-save handles this common case ‘}’ but…
        1. NQZK1H: ~4min ago, editor hung in ‘saving draft’ so refresh then did ‘Post restored to revision from July 3, 2015 @ 15:50:27 [Autosave]’
      14. NQX54T: start more additions
      15. NQRJ4R: ‘{post.status.snapshot: date ’20150630Mon0802pst‘; after ID’ minutes 0~‘; revision ’4‘; words ’2615‘; version0.5‘; as ’>500 more words so time for a periodic save‘, do ’Update so publish 3‘}’.
      16. NQMCWX: ‘{post.status.snapshot: date ’20150627Sat1300pst‘; after ID’ minutes 1~‘; revision ’3‘; words ’2083‘; version0.4~‘; as ’time for a periodic save including NQMCN0(my Windows 8.1 Disk.Usage again at 99% {from possibly Chrome or/and Insync} so need-else-good to restart Chrome & maybe OS)‘, do ’Update so publish 2‘}’.
      17. NQK1RU: ‘{post.status.snapshot: date ’20150626Fri0704pst‘; after ID’ minutes 1~‘; revision ’3‘; words ’628‘; version0.3~‘; as ’want to add content __‘, do ’recover from auto-save then start editing‘}’.
      18. NQGFEB: ‘{post.status.snapshot: date ’20150624Wed0808pst‘; after ID’ minutes 1~‘; revision ’2‘; words ’463‘; version0.2~‘; as ’want to add content __‘, do ’start editing‘}’.
      19. NPE16C: ‘{post.status.snapshot: date ’20150603Wed1432pst‘; after ID’ minutes 4~‘; revision ’1‘; words ’510~‘; version0.2~‘; as ’need to restart Chrome‘, do ’save & 1st publish‘}’.
      20. NPE0M1: category creation –see cat hierarchy
      21. NPB9QR: adding initial content
      22. NPB9F6: ‘{post.status.snapshot: date ’20150602Tue0238pst‘; after ID’ minutes 1~‘; revision ’1‘; words ’222~‘; version ’0~‘; as ’stuff to record & ideally share‘, do ’“now {via “Copy to a new draft”, so of {then latest post template used by author, so /4658#KOLWCZ}, so of then its latest saved version, so with last entry “/4658#NOMI95: ..”} created {this here post, so” http://1.JotHere.com/4706 “} then cut {all its content not to be reused here, so the content just applying to the template, so all the posts’ fully stated points including their KCGUIDs}.”‘}’.
      23. NPBXUP: “post edits overall history table, by increasing start-time”
        “entry ID” “action” “why” “word cnt” “ver #” “s#” “date (typ fr earliest ID)”
        1. O3NCNC: –content moved out per :O3NA0G
    3. NYM1YF: To reply & discuss, please use JotHere.com’s default methods (click) except: none.
    4. NYM1X1: TOU(Terms Of Use) including {copyright © and info’s {confidentiality/privacy & allowed use}} is JotHere.com’s default TOU except: none.
      1. O3NAUR: section id change from ‘NPB9KG: ’ to the standard
    5. O3OXM6: major subs: ‘overall :NQK6MZ’ + ‘versions :NQGFHA’ + ‘underlying algorithms and especially scalability :NQRGOX’ + ‘formulas :NQM273’ + ‘rows or/and columns :O3OXRH’ + ‘custom scripting :NQK4VH’ + ‘custom-results URL :NPBA73’ + ‘query language :NPB9TJ’ + ‘much more TBA here :NPBAYU
    6. ‘additional’
      1. NZ3GR7: ‘name anew’
        1. O3NO8I: title: present
        2. NPB9NN: title: now originally to next: ‘Google Spreadsheet stumbling points, by DestinyArchitect
        3. O3DAXT: ‘‘section history additional’ ‘in reverse start order’:’ :O3DAXT
          1. O3NAWZ: section id change from ‘NPB9LE: ’ to the standard.
      2. NYWZWX: ‘post category’
        1. O3NBKW: section id change from ‘NPE11O: ’ to the standard
        2. NPE0WT: ‘Google Spreadsheet NPE0SI’
        3. NPE0XF: ‘DestinyArchitect creation N0L9VW’
      3. O3KZ33: ‘success’
      4. O3DC5I: ‘advertising/lessening additional’
      5. O3KZ2Q: ‘motivation’
      6. NWV4HX: ‘contributors including their {contributions & relevant background}’
      7. NZ3KAG: {to be put elsewhere} so ‘annex’
  2. NQK6MZ: overall :NQK6MZ
    1. NQK6R6: I use it
      1. NQK6SF: ~1 hour every day since ~2009 (so not long after its 2006.06.06 1st release)
      2. NQK6TB: for about 1/2 of my critical data
      3. NQK6UH: including have written ~50K words of ‘custom scripting’, some very sophisticated
    2. NQK6NC: the best online spreadsheet I know of, including
      1. NQK6OK: exceeds the 2nd best I’ve heard of , Zoho Sheets, due to notably better ‘custom scripting’
    3. NQK6XI: has limitations, some very painful
      1. NQK6YC: ~60% just from the knowledge representation flaws/limitations of spreadsheets
      2. NQK707: ~40% from design flaws & other bugs, to be detailed in this post
  3. NQGFHA: versions :NQGFHA
    1. NR1B9Y: determining which version
      1. NQGFHM: fail have any official version numbers
        1. NQGFI8: probably because some GUI jerks at Google mis-thought & still mis-think this would not be necessary –perhaps that online apps are excepted.
        2. NQGFK3: forcing the user to look for semi-cryptic clues as to which version any particular instance is.
        3. NQGFLQ: consequently the version numbers are here guestimates
      2. NR1E96: at least now (so thru ‘(version) “2”’), there appears to be no official way to programmatically determine the version #
        1. NR1EC7: including https://developers.google.com/s/results?q=version+sheet&p=%2Fapps-script finds no method-call to check that.
        2. NR1EFZ: so
          1. NR1I7U: use feature tests (as for V2)
            1. NR1EG6: I writing script do it: ‘function Spreadsheet_Version_NR1BKT(spreadsheet)’
              1. NR1WJL: for every version being considered, tests effectively-qty n methods reported & ideally-found to work only in that version
                1. NR1WK0: so far, n=2
              2. NR1WKJ: only reports a particular version being the case if all tests for all versions (not just it) are consistent for that, otherwise reports the test results so once can correct the tests/testing.
    2. NQGFN8: ‘(version) “1”’: version “1”
    3. NQGFO0: ‘(version) “2”’: version “2”
      1. NQGFUT: announcement https://support.google.com/docs/answer/3541068?rd=1
        1. NR1BCJ: programmatic announcement https://developers.google.com/apps-script/migration/sheets
      2. NR1BF1: 1st release date: 2014.03.21~ as (here in order started):
        1. NR1DMX: portion (Add-ons) to developers on 2014.03.11.
        2. NQGFWE: ‘Starting in March 2014, the new version of Google Sheets will become the default for all users when creating a new spreadsheet’ says NQGFU1
        3. NR1DVV: ‘Starting in March and April 2014, Google Sheets users creating new spreadsheets will be automatically directed to the new Sheets.’ says NQGFUT
      3. NQGFOS: from ‘(version) “1”’
        1. NQGFR2: upgrading
          1. NQGG8G: automatically
            1. NQGG8Q: as of now (2015.06.24), I’ve seen this done on ~20% of my spreadsheets
          2. NQGGAH: manually
            1. NQRFPD: method: copy a ‘(version) “2”’ sheet into the ‘(version) “1”’ spreadsheet to be upgraded, then that whole spreadsheet instantly upgrades
              1. NQRFPY: the best manual solution if it really works (a test)
              2. NQRFQP: usage
                1. NQRFXU: I accidentally discovered this as detailed at NQOPBZ of ‘NQOPAR’
                  1. NQRGML: to confirm indeed the whole spreadsheet got upgraded and not just the copied-in-sheet, run tests
                    1. NQRGMU: not yet done.
            2. NQGGEE: http://webapps.stackexchange.com/a/69119/93832
              1. NQGGHM: works
              2. NQGGIM: best solution known so far
            3. NQGFU1: NQGFU1: official https://support.google.com/docs/answer/3544847?hl=en –incomplete
              1. NQGGBM: fails to handle sheets linking to other sheets
        2. NR1GXN: methods that vary in support between this version & the previous
          1. NR1GXW: all-else-most listed in NR1BCJ
          2. NR1GZA: good to detect version
            1. NR1H32: NR1H32: if method operates on global SpreadsheetApp seems bad as both:
              1. NR1H3Z: that isn’t specific to the spreadsheet (so possibly an old spreadsheet could have new)
              2. NR1H5F: there seems no way to map from a spreadsheet object and its SpreadsheetApp (as that’s global).
            2. NR1PKA: only in previous version
              1. NR1PHL: ‘Spreadsheet.isAnonymousView()’: Spreadsheet.isAnonymousView() being depreciated, ref’d by NR1GXW
                1. NR1RTB: in ‘(version) “1”’ found now runs correctly but produces yellow debugger popup ‘Method Spreadsheet.isAnonymousView is deprecated. Expand File: NINQ2L Line: 196’ so if does the same for next version, won’t be a good test.
                2. NR1V8E: precisely depreciated 2014.01.13
                3. NR2PBK: in ‘(version) “2”’, find now produces error ‘The api method ‘isAnonymousView’ is not available yet in the new version of Google Sheets.’, trying to see if this is catchable
              2. NR1UCA: ScriptGallary –last archive copy ‘since NR1URK’— ref’d by NR1GXW, appears to have no API so won’t work.
              3. NR1UI4: Spreadsheet.isReadable() –last archive copy ‘since NR1URK— ref’d by ref’d by NR1GXW,
            3. NR1PJ9: only in new version, from roughly most preferred:
              1. NR1J07: ‘Spreadsheet.getProtections(type)’: ‘Spreadsheet.getProtections(type)’ ref’d by NR1IGI:
                1. NR1RS5: in ‘(version) “1”’ found now produces ‘{e=Exception: The api method ‘getProtections’ is only available in the new version of Google Sheets.}’, so looks like it will be a usable test.
              2. NR1VQI: Range.getDataSourceUrl() 1st ref’d by near NQKQ0T in my .gs
                1. NR1VTC: precisely added 2014.04.03
                2. NR1W6P: –code line trying this
              3. NR1I5W: Range.isStartColumnBounded()’ found from NQKQ0T in my .gs, )
                1. NR1IGI: search finds also referenced here
              4. NR1GZN: uiApp ref’d by here: NO per NR1H32
              5. NR1H12: DataValidationBuilder.requireFormulaSatisfied(String)’ ref’d by here: NO per NR1H32
            4. NR1PRG: code step NR1JX6 now finds {spreadsheet.isAnonymousView & spreadsheet.getProtections & perhaps more/all} return real functions in ‘(version) “1”’, so their existence can’t be used to tell which version.
  4. O3OY1P: use
    1. O3OYUA: Add-ons
      1. O3OYUW: comparison in table ‘user-rated {software including plugins, extensions, addons, apps} :NMMDBV’
    2. O3OY2D: mail-merge and row-to-report generation :O3OY2D
      1. O3JK03: off-the-shelf package
        1. O3JK5G: Spreadsheet Add-ons
          1. O3JK75: ref’d #1 by O3JK2N
          2. O3JK9T: search ‘tag’ (short for name-tag) :O3JK9T
          3. O3JK8J: ‘Mail Merge & Doc Merge with Team Features’ :O3JK8J
            1. O3KPL9: ref #1 O3JK9T find 3
      2. O3JK2A: GS(Google spreadsheet label) :O3JK2A
  5. NQRGOX: underlying algorithms and especially scalability :NQRGOX
    1. NQRGPI: For most proprietary software, quite unfortunately (indeed I’d argue it should be illegal) makers don’t give the algorithm analysis, especially Big O for time & space, nor reveal the algorithms, making knowing the software’s scalability guesswork.
      1. NQRGWS: Google Spreadsheets seems no different
    2. NSRZMM: bugs killing scalability
      1. NSRZVR: severely buggy recalc implementation in at least ‘(version) “2”’
        1. NSVULQ: where impacts: in important cases, at least involving a every row calling a custom function
        2. NSVUI1: kill scalability
        3. NSVUJ3: abuses use & users via 3 cross-reinforcing methods (leaving users no real workarounds other than leaving the product):
          1. NSRZWT: forcing use of auto-recalc including no way to trigger nor even add explicit/manual recalc –very bad
            1. NSRZX8: –insanely arrogant: the arrogant designers didn’t include a button/specifier for manual recalc as they foolishly & very mistakenly thought their, or any, automatic algorithms could always handle it, and even though they were basing their design on then industry monopoly Excel which always had a recalc button.
            2. NSRZY8: so attempts to do it automatically, but fails including:
              1. NSS008: example NSRZN5, with no way to force recalc as no recalc button!
                1. NSS087: Because it’s apparently busy recalcing every cell which was or may soon be displayed, and 2nd because they set the resource usage max low, the few cells one want to see typically don’t get calced so are a mess.
          2. NSVUAX: forcing calc resources to be severely limited
            1. NSRZN5: failure increasingly worse starting ~392 rows
          3. NSVUF5: forcing inefficient recalc:
            1. NSS2R3: frequently recalcs when no recalc is needed
              1. NSS2SR: so wasting the CPU (which its designers find so precious they must place low limits on)
              2. NSS30M: again, with manual recalc, {recalcing too much from not recalcing when needed} doesn’t have to be a problem as then the user can tell when recalc needed.
              3. NSVU3Z: when row order changed by select & drag even though no computation result will change (because nothing depends on the row order including cell references are appropriately adjusted)
              4. NSVU6P: apparently every time the sheet is loaded including refreshed
                1. NSVU8C: arbitrarily expensive especially with deep including recursive calculations, as are useful so I do
                2. NSVU8X: instead calculated values should be persisted
    3. NQRI8Z: Google’s official specs
      1. NQRI9G: wrongly only give size limits, not how speed & storage is affected by size
      2. NQRIB6: as far as ‘(version) “2”’, merely says the limit is ‘Spreadsheets: 2 million cells.’
      3. NQRIAG: only official docs I’ve found so far
    4. NQRIFN: storage size
      1. NQRIGV: knowing this can well help determine how long it would take to open, close, & save a spreadsheet or sheet
      2. NQRISY: unfortunately I’ve seen Google giving no specs here (on storage size)
        1. NQRITN: even the file size in http://drive.google.com is undisplayed (instead it just displays ~‘no charge’)
        2. NQRIVG: This is likely because Google unlimited free online storage for everything stored in Google Docs, which then includes Spreadsheets.
          1. NQRIX3: however that’s not good enough reason as the info is key to predicting performance (example).
    5. NQRGY9: What all data does the spreadsheet load in the browser & how?
      1. NQRIIH: what portions load before opening?
        1. NQRILO: For non-mobile OSes?
          1. NQRIM0: it appears to load from row 1 increasing in blocks of several hundred rows, where it looks like enough blocks load as described to display whatever the user is scrolled to, then allowing the user to explore & edit that data, while additional blocks load in the background until (typically fairly soon) the whole sheet is loaded.
      2. NQRH5J: in to the browser’s JavaScript RAM?
        1. NQRH04: The whole sheet or the whole spreadsheet?
          1. NQRH0X: For non-mobile OSes?
            1. NQRH1V: This seems to be the case based on informal tests:
              1. NQRH3L: When having very large sheet loaded in the browser then using Click & Clean with these options so deleting most significant local persistent storage , the sheet requires signin again naturally but does not appear to reload, suggesting the entire thing is in RAM.
          2. NQRI21: For mobile OSes
            1. NQRI31: there is often not so much storage so probably used is a partial-load -what exactly?
            2. NQRI2I: as with using Google Sheets app
    6. NR1VIL: every script in ‘(version) “2”’: can run max 6 minutes.
    7. NQRI4H: more info hopefully to be found.
  6. NQM273: formulas :NQM273
    1. NQM27Q: on arrayformula: on arrayformula
      1. NQM2CY: for repeating formulas
        1. NQM2I3: other solutions with potentially no persistent storage cost for each default-value repeat
          1. NQM2L9: none I found
          2. NQM2M8: may not be any others so far
        2. NQM32D: explanation & example given at https://superuser.com/questions/260527/how-to-create-a-formula-for-every-row-in-a-column-in-google-spreadsheet/562062#562062
        3. NQM332: where ‘sheet calling a custom function’
          1. NQM4VS: NQM4VS: failing
            1. NQM4WH: so apparent bug
            2. NQM4WP: at least for versions
              1. NQM4X4: ‘(version) “2”’
                1. NQM4YX: minimal demo: this sheet column NQM4JJ
            3. NQMIQZ: notable workaround per here
            4. NQX56H: Stack Exchange report http://stackoverflow.com/questions/27002683/applying-a-custom-function-to-an-entire-formula-without-arrayformula
              1. NQX5AT: has auto copy & paste workaround that then naturally will require maintenance and force space waste for formula.
                1. NR19OY: my 1st use of this: column ‘MEWW76 NR0V6W .. Like ratio (1 is totally liked, .5 is neutral, 0 is totally disliked) ..’ of the NIRT92( so ‘Show(Movie/Film/Video,Play,or other)’ reviews of MEY2HI) updated via new ‘function MEY2HI_NINQ3J_Set_NR0VH0’
  7. O9D6A4: cell
    1. O9D6AI: display
      1. O9D6B2: of computed value
        1. O9D6BL: full including selectable
          1. O9D6CB: when (cell) is 1st selected cell
            1. O9D6E7: is very much needed especially when the cell contains a formula
            2. O9D6D9: missing (from all spreadsheet makes I’ve seen)
              1. O9D6FC: instead all that’s displayed is the formula, or source-text, and without formatting
              2. O9D6GP: workaround _
                1. O9D6HB: by me
                2. O9D6HG: menu item ‘do ActiveCell_value_display_LONN9K LONNNP’ creates a popup with its contents
                3. O9D6KO: slower & must be manually selected and misses intended formatting, but otherwise does the job.
        2. O9D6MB: mode wrap
          1. O9D6NH: of each line where the next word will partially fit does 1 of:
            1. O9D6OV: starts the word on the next line
              1. O9D6PM: is what it currently does
              2. O9D6Q4: compared to O9D6W6, wastes rest of line and makes text hard to read & take up to 2x more lines

                1. O9D6TM: so is bad so BUG
                2. O9D71Y: workaround _
                  1. O9D734: have the data in the cell be the result of computing {wrappable_O9CLBW, which, replaces every ‘ ’ with a   and also prefixes with the original so text is still findable)
                  2. O9DIOR: –so far sadly fails due to bug O9D79G
            2. O9D6UP: continues present word until no more of its characters fit, then end with a soft hyphen or ‘\’ or nothing
              1. O9D6W6: is notably near-max efficient & most readable :O9D6W6
                1. O9D6RW: especially when width is narrow, which is the norm for {Y/N and short numerical columns –extremely common}
                2. O9D702: so is reasonably needed
          2. O9D77Y: cell height minimum is 1 of:
            1. O9D78F: the minimum height which will still cause the full content to be displayable
              1. O9D79G: is what it currently does :O9D79G
              2. O9DIH2: so prevents any truncation –regularly bad so a bug.
              3. O9DIIE: rather this setting should be achieved by 1 or more:
                1. O9DIKO: introduce a new dimension setting of ‘size to content’
                2. O9DIMF: statically set (the height) to this point, notably as normally done in spreadsheets via shift-drag (or similar) for resizing
            2. O9D79U: any size, where, iff too small/large then {bottom if left justified, top if right justified} is-truncated/has-blank-area respectively
              1. O9D7D0: is what is needed & appropriate
  8. O3OXRH: rows or/and columns :O3OXRH
    1. NQMSJF: moving rows
      1. NQMSJW: is done by select & drag
      2. NQMSK6: reference maintaining
        1. NQMSKQ: BUG: deletes range references
          1. NQMSSQ: at least for versions
            1. NQMSTV: in ‘(version) “1”’:
              1. NQMSL5: at least in this sheet (private), in rows NQH6JO NNN2JB, respective formulas ‘=arrayformula(D240:AAZ240)’ & ‘=Range_Json_N6NNF5(240:240)’ become ‘=arrayformula({}:{})’ & ‘=Range_Json_N6NNF5({})’ when row 240 is dragged up a few rows.
              2. NR1A3Q: in data of NR19OY before that fix applied, presumably from rows being dragged up or down, had ~11 formulas (1 per row, starting with row MEX2RG and ending at rowMLG568) corrupted per pattern (sampling 1st one) ‘=RankRow_LikeRatio__NINQ3J(row($11:$11),row($240:$240),row($272:$272),row({}))’ where ’row(22:22)’ becoming ‘row({})’, so also showing only relative, not absolute, ranges were corrupted.
    2. NQM5QK: sorting
      1. NQM5QV: BIG BUG: normally corrupts references to all data moved by the sort
        1. NQM752: at least via means
          1. NQM779: via the GUI: menu option ‘Data.Sort..’
            1. NQM787: –so via the most obvious means for sorting
            2. NQM7ZS: likely inheriting the problem from sort()
          2. NQM7KQ: via sort()
            1. NQM7L3: don’t know other than would suspect it was as would imagine, for minimal code, the GUI means executes this
            2. NQM7VV: so seemingly the only means to automatically sort source data…
              1. NQM7ZL: short of ‘custom scripting’ to do replace the built in sort()
                1. NQM82Y: which seems a complex & large undertaking and which still would run notably slower (using higher-level script code do a fairly primitive & large operation)
          3. NQM7GW: not via Query command nor sort function as that isn’t actually moving the data but make a reordered copy
            1. NQM7PN: but very often one wants to edit the sorted report, not just view it, but such copy doesn’t readily allow editing as it is uneditable itself and, most problematic, doesn’t drill-down to and/or allow editing of the source
        2. NQM5RL: at least for versions
          1. NQM5S6: thru ‘(version) “2”’
        3. NQM79I: other spreadsheet makes I’ve tried (Excel, Libre/Open Office) I recall it happening, too.
        4. NQM7AZ: fixable
          1. NQM7CU: by the user: hard
          2. NQM7BM: via spreadsheet app programmers: seems fairly easy, just a tad bit of algorithmic complexity
            1. NQM7DN: so why don’t they fix it? I have no idea.
        5. NQM5SU: bug report at http://webapps.stackexchange.com/questions/75457/formulas-mix-up-references-when-sorting-data-in-google-spreadsheet
          1. NQM68T: my latest response
            1. NQM67K: per below, the latest quote of it (except links make working & for clean display, as here that’s possible):

              Outstanding point & question! I fully second this, indeed find this a needless & terrible bug of Google Spreadsheet and other spreadsheets.

              And IMHO, at least a start of good indeed often outstanding answers is finding & looking-at the existing work the problem, so… other web threads on this problem:

              1. …found via Google Search(Google spreadsheet sorting references) page 1: plenty:
                1. Sort all rows from one column data with external references
                2. “How to maintain reference to data when sorting referenced data”
                3. “Data Sorting is not working correctly.”
                4. “Google Spreadsheet Sorting Bug”
                5. “The sort feature in spreadsheets with data from other spreadsheets or sheets”
                6. “Sort a sheet that has indexed references to other sheets”
                7. “arrayFormula and re-sorting…”
                8. “Sorting in a Spreadsheet: Don’t lose cell references”
                9. “maintaining cell references across multiple sheets when sorting”
              2. More likely; append your finds to the above!
              share edit undelete flag
              your post was converted to a comment; see the help center.
            2. NQM66E: there (on WebApps.StackExchange.com) this answer keeps getting wrongfully hid due to poor/deconstructive site policy and/or execution of it
              1. NQM6QS: as (contrary to what is being done there) links alone can be and frequently are the entire answer else a great start on finding it, including a maximally compact & sourced answer!
  9. NQK4VH: ‘custom scripting’: custom scripting :NQK4VH
    1. O9EY4M: is (for all I’ve seen) 100% done in ‘GAS(Google Apps Script) .. /5556#O9EWSK’ –so for details of that not specific to this use, see that.
    2. NQK1VI: ‘sheet calling a custom function’: sheet calling a custom function
      1. NQM4UQ: is normally key
        1. NQM57U: as typical functions & operations used in production are reasonably complex & change to degree that it is not possible do and/or maintain them via the built-in formulas
      2. NQM55U: breaking where it works for built-ins formulas
        1. NQM59Y: appears more often than I would like
        2. NQM5BS: examples: NQM4VS
        3. NSRVND: in recalc
          1. NSRVYJ: bug causing ‘‘Loading…’ with red error hint ‘Error<br/>Loading Data …’’
            1. NSRZA8: severity: worst
              1. NSRZB1: The most upsetting bug in Google Spreadsheets as
              2. NSRZBK: slows my productivity every day more than any other problem.
              3. NSRZJJ: Kills scalability of a useful (so for me common) types sheets (as with rows with a computed name) over ~392 rows, sometimes much less.
            2. NSRVYJ: where: ‘(version) “2”’ and probably ‘(version) “1”’ as coded by Google.
            3. NSRW35: Example: in sheet {item or model or category MNHZOG},
              1. NSRWF1: This sheet has, for almost every row, a call to a custom function to compute the name of item of the row,
                1. NSRWFI: for example ‘=name_full_NGUTR5(A392,M392,S392,AE392,BW392,D392,E392)’ to compute the name of the item of row 392.
                2. NSRWG6: This custom function is fast & simple, just a concatenation of its arguments (which are all short strings) depending on which are non-empty.
                  1. NSRWHB: indeed it probably still, and was originally, written a formula with just normal built-in function calls, but naturally that proved hard to read and very hard to keep in sync, so was appropriate converted to a custom function.
                3. NSRWJF: Nonetheless, by say this row (392), instead of the correct value it displays as ‘Loading…’ with red error hint ‘Error<br/>Loading Data …’ and will NEVER complete (always stays that) unless
                  1. NSRWNG: sometimes when the formula (indeed each instance manually & individually) is replaced where the formula call is replaced with different & seemingly new that ideally will compute the same result,
                    1. NSRZ8S: for instance, in this last example, replacing the ‘..,E392)’ with ‘..,E392,)’ if that form hasn’t been used significantly
                  2. NSRZDM: and as this spreadsheet has 552 rows and is steadily growing and randomly accessed, and increasing soon most rows reference the names of other rows, this means increasingly the average row is heavily unreadable including its name.
          2. NSRVO1: background
            1. NSRVOI: a formula using built-ins can be arbitrarily complex including some lookup functions could take time proportional to a sheet, but because there are no explicit loop constructs that I’m aware, it would seem that these generally an acceptable time to compute.
            2. NSRVWM: However a custom function can have custom explicit loops including recursion so can and readily do take arbitrary time & potentially resources, so calling them can be arbitrarily expensive.
            3. NSRVX0: so it appears (by the bugs I’m getting that Google has placed limits, indeed bad ones as NSRVYJ, on how much time these take.
      3. NQK1W8: which returns an array
        1. NQK1WS: displayed as if the call was wrapped in ArrayFormula(..)
          1. NQK1XW: happens automatically
            1. NQK1YK: so that wrapping is not necessary
        2. NQK1ZX: containing boxed values
          1. NQK54L: so nearly always caused by ‘lacking strict support’
          2. NQK20G: as the array ‘[[(new String(“NNKZ7A”)), (new String(“NQD12V”)), (new String(“”)),..]]’
            (vs. w/o boxing: ‘[[“NNKZ7A”, “NQD12V”, “”, ..]]’
          3. NQK21N: at least boxed strings will instead be displayed as ‘–’ so effectively not displayed
            1. NQK244: –a bug
            2. NQK2G5: happens in Google Spreadsheets version
              1. NQK2GM: ‘(version) “1”’
            3. NQK24T: workaround: rewrite the custom function
    3. NQKNFK: class Range: Range class
      1. NQKNGQ: isBlank
        1. NQKNHG: over a cell containing a formula which returns the empty string
          1. NQKNJZ: such formula which returns the empty string:
            1. NQKNK7: simplest examples, ‘=””&””’ & ‘=””’
            2. NQKNKE: common example: a formula where the results haven’t been computed yet as its displayed as ‘Thinking’, for instance many ‘sheet calling a custom function’
          2. NQKNNZ: returns ‘true’
            1. NQKNON: BUG: that appears to conflict with official definition ‘Returns true if the range is totally blank’
            2. NQKNQI: at least in version…
              1. NQKNQW: ‘(version) “1”’
                1. NQKNVS: seen at code NQKNAR and (actually not yet) NQKN0L
            3. NQKUP2: workaround: use a combo of getValue(s) and getFormula(s)
              1. NQKUPZ: example (semi-verified): ‘var cell_isBlank_Correct=function(this){return !(this.getValue()||this.getFormula())}’
  10. NPBA73: custom-results URL :NPBA73
    1. NPBAIP: https://mashe.hawksey.info/2013/07/how-to-open-a-google-spreadsheet-at-a-specific-row-for-editing/ –1st ref’d by NPB91H find 9
    2. NPBA8T: via query language
      1. NPB91H: NPB91H: GS(Google tq parameter) {}NPB91H}
      2. NPBAG6: NPBAG6: http://www.bohyunkim.net/blog/archives/2831 –1st ref by NPB7RS find 5
      3. NPBAM1: https://developers.google.com/chart/interactive/docs/querylanguage#setting-the-query-in-the-data-source-url –found manually after 1st hearing of tq via NPBAG6
      4. NPBAPG: where web page JavaScript retrieves Google Spreadsheet data in JSON (via JSONP)
        1. NPBAPY: https://coderwall.com/p/pluhsg/google-spreadsheet-json-api-sql-filtering –1st ref’d by NPB91H find 8
        2. NPBVZS: my leading conceived use
          1. NPBW0N: by query form: from ‘_ misc 2015.02 possibly.NJTYS5’ or equivalent structured table source, ‘select `{{entry}{name.full}{{reference}{{absolute}}} NNY0XH}`, where `{{entry}{context} NJU0YS} [but where every level has its ID here]` = ‘NNXWIF'[ or other target]’
        3. NQKQTM: the URL for a given Range
          1. NQKQTR: generator https://developers.google.com/apps-script/reference/spreadsheet/range#getDataSourceUrl()
            1. NQKQWI: a real result (with key redacted): https://docs.google.com/spreadsheet/tq?key=to……………..Qo-g&headers=-1&transpose=0&gid=4&range=C228:C228
  11. NPB9TJ: query language :NPB9TJ
    1. O3OXYV: official docs https://developers.google.com/chart/interactive/docs/querylanguage
    2. NPB9T0: Query command
    3. NPBA31: identifiers
      1. NPB6Y7: tragically https://developers.google.com/chart/interactive/docs/querylanguage#identifiers is false;
        1. NPB7YL: for instance, NPAU7W when ‘E’ replaced with ‘`NO3GUO`’ or ‘NO3GUO’ produces ‘Error Unable to parse query string for Function QUERY parameter 2: NO_COLUMNNO3GUO’
        2. NPB7YM: reported best so far at https://groups.google.com/d/msg/google-visualization-api/FMGmbzrhh_4/HaFLBFWZF5EJ –1st ref’d by NPB7RS find 3
        3. NPBAT4: my workaround
          1. NPBAUV: 1st use in Query__NP3F3L in ‘misc 20150504180111.NNUR36’
          2. NPBAV3: works pretty well so far
          3. NPBAVG: TBA
      2. NPB7RS: NPB7RS: GS(Google query language identifiers)
    4. NPBZYE: in Pouch DB
      1. NPBZYM: query language redoc for at least Pouch (high rated) https://github.com/pouchdb/pouchdb/wiki/Google-Query-Language-Documentation –1st ref’d by NPB7RS find 7
    5. NR1URK: footnote ‘since NR1URK’: archive copy linked here since Google wrongly longer hosts the info (wrong since healthy is to have no reasonably-avoidable info loss)
  12. NPBAYU: much more TBA here :NPBAYU