Google spreadsheet translation status?!

If it's not ZDoom, it goes here.

Google spreadsheet translation status?!

Postby Tormentor667 » Thu Nov 26, 2020 3:27 pm

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
User avatar
Tormentor667
needs more detail
 
Joined: 16 Jul 2003
Location: Germany

Re: Google spreadsheet translation status?!

Postby Nero » Sat Nov 28, 2020 7:40 am

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.
User avatar
Nero
Royal Boredom....Why can't I do this in Windows?
 
Joined: 06 Sep 2006
Location: Middle of Nowheresville Il.

Re: Google spreadsheet translation status?!

Postby Gez » Sat Nov 28, 2020 8:04 am

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.
Gez
 
 
 
Joined: 06 Jul 2007

Re: Google spreadsheet translation status?!

Postby Nero » Sat Nov 28, 2020 9:23 am

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.
User avatar
Nero
Royal Boredom....Why can't I do this in Windows?
 
Joined: 06 Sep 2006
Location: Middle of Nowheresville Il.

Re: Google spreadsheet translation status?!

Postby Tormentor667 » Sun Nov 29, 2020 3:18 am

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"?
User avatar
Tormentor667
needs more detail
 
Joined: 16 Jul 2003
Location: Germany

Re: Google spreadsheet translation status?!

Postby Nero » Sun Nov 29, 2020 7:19 am

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:
User avatar
Nero
Royal Boredom....Why can't I do this in Windows?
 
Joined: 06 Sep 2006
Location: Middle of Nowheresville Il.

Re: Google spreadsheet translation status?!

Postby simc » Sun Nov 29, 2020 12:00 pm

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
Last edited by simc on Tue Dec 01, 2020 12:36 pm, edited 2 times in total.
User avatar
simc
 
Joined: 03 Feb 2012

Re: Google spreadsheet translation status?!

Postby Tormentor667 » Mon Nov 30, 2020 12:33 pm

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
User avatar
Tormentor667
needs more detail
 
Joined: 16 Jul 2003
Location: Germany

Re: Google spreadsheet translation status?!

Postby Nero » Mon Nov 30, 2020 3:13 pm

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.
User avatar
Nero
Royal Boredom....Why can't I do this in Windows?
 
Joined: 06 Sep 2006
Location: Middle of Nowheresville Il.


Return to Off-Topic

Who is online

Users browsing this forum: No registered users and 0 guests