=IF(DATEDIF(A1, B1, “D”)>365, QUOTIENT(DATEDIF(A1, B1, “D”), 365)&” year(s) “& QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30)&” month(s) “& MOD(QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30), 30)&” day(s)”, IF(DATEDIF(A1, B1, “D”)>30, QUOTIENT(DATEDIF(A1, B1, “D”), 30)&” month(s) “& MOD(DATEDIF(A1, B1, “D”), 30)&” day(s)”, DATEDIF(A1, B1, “D”)&” day(s)”))
This is a very common problem when dealing with auto-inserted data through sheets api or Google forms. The easiest solution would be to convert all your formulas into arrayformulas. For eg, Your formula in D2 =MID(C2,1,2)&”.”&MID(C2,3,2)&”.2020 can be modified as In D1: =ARRAYFORMULA({“Extracted Date”;MID(C2:INDEX(C:C,COUNTA(C:C)),1,2)&”.”&MID(C2:INDEX(C:C,COUNTA(C:C)),3,2)&”.2020″}) OR using regex: =ARRAYFORMULA({“Extracted Date”;REGEXREPLACE(C2:INDEX(C:C,COUNTA(C:C)),”(\d{2})(\d{2}).*”,”$1.$2.2020″)}) The table then becomes: | | … Read more
Google Apps Script is a subset of JavaScript, spreadsheet functions are currently not supported. For example, if you want to create a function that returns today’s date you should write : function test_today(){ return new Date() }// note that this will eventually return a value in milliseconds , you’ll have to set the cell format … Read more
AND doesn’t work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions. I.e. it checks if “”>”” which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down. You can use multiplication of … Read more
Similar answer to caligari’s answer, but we can tidy it up by just specifying the full column range: =INDEX(G2:G, COUNT(G2:G))
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
try: =ARRAYFORMULA(TEXT(SUM(IFERROR(TIMEVALUE(C:C))), “[h]:mm:ss”)) spreadsheet demo
I made a couple of adjustments to Mogsdad’s answer: Fixed the releaseLock() call placement Updates (or adds) a querystring parameter to the url in the import function (as opposed to storing, removing, waiting 5 seconds, and then restoring all relevant formulas) Works on a specific sheet in your spreadsheet Shows time of last update … … Read more
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
first, let’s see what says the fox =WHATTHEFOXSAY() is a unique easter egg google sheets function (discovered by @kishkin) that randomly generates a pre-set string of text on user demand which is a huge deal because while the generation is random, the recalculation is not affected by onEdit, onChange nor onOpen events, so with some … Read more