script: function SHEETLIST() { try { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() var out = new Array( sheets.length+1 ) ; out[0] = [ “NAME” , “#GID” ]; for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = [sheets[i-1].getName() , sheets[i-1].getSheetId() ]; return out } catch( err ) { return “#ERROR!” }} formula: =SHEETLIST() … Read more
Assuming no more than 100 rows try this “array formula” to count the different names in A2:A100 where there is a 1 in the same row in B2:B100: =SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>””,MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1)) confirmed with CTRL+SHIFT+ENTER Note that I say different not unique as the two are not the same
TITLE: =IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”,”//*[@id=’eow-title’]”) =REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(A12), 500, 1), “where Col1 contains ‘/title'”, 0), “>(.+)<“) VIEWS: =VALUE(REGEXREPLACE(TEXT(IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”, “//*[contains(@class, ‘watch-view-count’)]”),0),” view(s)?”,””)) DURATION: =SUBSTITUTE(REGEXREPLACE(IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”,”//*[@itemprop=’duration’]/@content”),”PT|S”,””),”M”,”:”) LIKES: =IF(ISNA(IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”,”(//*[contains(@class,’like-button-renderer-like-button’)])[1]”))=TRUE,0, IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”,”(//*[contains(@class,’like-button-renderer-like-button’)])[1]”)) DISLIKES: =IF(ISNA(IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”,”(//*[contains(@class,’like-button-renderer-dislike-button’)])[1]”))=TRUE,0, IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”,”(//*[contains(@class,’like-button-renderer-dislike-button’)])[1]”)) UPLOADED: =REGEXREPLACE(IMPORTXML(“https://www.youtube.com/watch?v=MkgR0SxmMKo”, “//*[contains(@class, ‘watch-time-text’)]”),”((Uploaded)|(Published)|(Streamed live)) on “,””) SUBSCRIPTIONS: =IFERROR(MID(QUERY(IMPORTXML(“https://www.youtube.com/channel/”&A1, “//div[@class=”primary-header-actions”]”), “select Col1”), 31, 20), ) CHANNEL NAME: =INDEX(IMPORTHTML(“https://www.youtube.com/channel/UC7_gcs09iThXybpVgjHZ_7g”,”list”,1),1,1) CHANNEL ID: =ARRAYFORMULA(REGEXREPLACE(QUERY(SUBSTITUTE(ARRAY_CONSTRAIN( IMPORTDATA(https://www.youtube.com/watch?v=rckrnYw5sOA), 3000, 1), “”””, “”), “where Col1 contains ‘<meta itemprop=channelId … Read more
QUERY level 1: if all 5 cells in range C2:G have values: =QUERY(QUERY(C2:G, “select (C+D+E+F+G)/5”), “offset 1”, ) if not, then rows are skipped: if empty cells are considered as zeros: =INDEX(QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )) to remove zero values we use IFERROR(1/(1/…)) wrapping: =INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )))) to make Col references … Read more
Issue: Undesirable addition of empty strings in all the available rows by traditional usage of ARRAYFORMULA(IF(A:A=””,…)) Solution: Using ARRAYFORMULA properly with INDEX/COUNTA(to determine the last row that’s needed) ensures formula is only filled upto the needed row instead of a camouflage INDEX/COUNTA: INDEX returns a value as well as a cell reference. A2:INDEX(A2:A,COUNTA(A2:A)) => If … Read more
internal custom number formatting solution: sadly, the internal formatting in google sheets is by default able to work with only 3 types of numbers: positive (1, 2, 5, 10, …) negative (-3, -9, -7, …) zero (0) this can be tweaked to show custom formatting like thousands K, millions M and regular small numbers: [>999999]0.0,,”M”;[>999]0.0,”K”;0 … Read more