Page 1 of 1

Google spreadsheet translation status?!

Posted: Thu Nov 26, 2020 3:27 pm
by Tormentor667
Dear ZDoomers,

I know this is not a Common issue for this forum but as the translation/localization of GZDoom is also organized with a google spreadsheet table, I just thought it makes sense to ask here.

For Blade of Agony we use the same concept. We have a complete English spreadsheet in one column and additionally other languages in other columns. Now I wanted to know if there is some kind of a formula that checks how many cells are empty compared to the English column and then tells how much percentage of a language is translated. For example „English is 100%, German is 95%, Czech is 60% translated“

Is something like that doable?

Best regards,
Torm

Re: Google spreadsheet translation status?!

Posted: Sat Nov 28, 2020 7:40 am
by Sarah
Google Spreadsheets is basically Microsoft Excel, so their formula language is "usually" compatible. Check out this page on if a cell is blank. Might be a less than elegant solution, I'm already thinking the formula will be pretty ugly, or even worse you'll have to have a second cell for formula result storage.

Re: Google spreadsheet translation status?!

Posted: Sat Nov 28, 2020 8:04 am
by Gez
Note that on the GZDoom/Raze spreadsheet, there's some special colors that are used to signify a translation is deliberately missing (the word should not be translated, or the translation is identically written). Dunno if spreadsheet formulas can take this kind of meta value into account.

Re: Google spreadsheet translation status?!

Posted: Sat Nov 28, 2020 9:23 am
by Sarah
Do you mean Conditional Formatting? Or that certain cells just have a particular color applied?

I thought there was some way to get the background color, so it shouldn't matter how the color gets applied. Again, this is Excel, not Google Spreadsheets, so there are/will be differences in functionality.

But, basically, check if a cell is colored, if it's not add it to the count of cells in the percentage calculation. The only real problem I see is checking if a range of cells are blank, I'm worried this has to be done one reference at a time, so that means the color check does too. :shock: :? :evil: I do not like suggesting solutions that cannot be optimized nicely.

Re: Google spreadsheet translation status?!

Posted: Sun Nov 29, 2020 3:18 am
by Tormentor667
Thanks kindly for your replies. I was hoping for an easier solution, as I am not used to EXCEL formatting at all. So I guess this is just one of the features "Nice to have but too much effort"?

Re: Google spreadsheet translation status?!

Posted: Sun Nov 29, 2020 7:19 am
by Sarah
Eh, I've seen worse.

Check this test spreadsheet out Torm. Cell A13 does the magic, it's not elegant, and it doesn't deal with colors, but it does calculate a percentage of completed cells :wink:

Re: Google spreadsheet translation status?!

Posted: Sun Nov 29, 2020 12:00 pm
by simc
Howabout:
=counta(a12:a34) to count all the non-empty cells from the selection?
=countblank(a12:a34) to count the empty cells?

As Gez sain, but atleast they might allow some rough comparison. If for example the column M for one language has all of it's translations ready and the next column N has only part of them, the ratio would be:

=counta(n3:n45)/counta(m3:m45)

---------------------------
Added note, if your OS or just spreadsheet program (Excell/Libreoffice Calc) uses other language than english, also the function names might be translated and the english written function name might not work.

english: COUNTA()
germany: ANZAHL2()
french: NBVAL()
spanish: CONTARA()
swedish: ANTALV()

english: COUNTBLANK()
germany: ANZAHLLEEREZELLEN()
french: NB.VIDE()
spanish: CONTAR.BLANCO()
swedish: ANTAL.TOMMA()

Google Sheets function list:
(in english): https://support.google.com/docs/table/25273?hl=en
(in german): https://support.google.com/docs/table/25273?hl=de

Re: Google spreadsheet translation status?!

Posted: Mon Nov 30, 2020 12:33 pm
by Tormentor667
Nero wrote:Check this test spreadsheet out Torm. Cell A13 does the magic, it's not elegant, and it doesn't deal with colors, but it does calculate a percentage of completed cells :wink:

Do you have an idea how to adept this to this spreadsheet? Meaning that in the columns "de", "es", "it" etc. were compared to the "enu" column and at the very bottom it says "80%" is translated for example.

Link: https://docs.google.com/spreadsheets/d/ ... =699002033

Re: Google spreadsheet translation status?!

Posted: Mon Nov 30, 2020 3:13 pm
by Sarah
I was wondering if it'd be easier to let me have access to the workbook you need this for. Access to the link is denied, I will PM you my Google account info.