I like to keep it simple. If A1 holds the date and B1 holds the number of months to add, then =date(year(A1),month(A1)+B1,day(A1)) would calculate the required result. The same way could be used for days or years
After some update in 2020 all codes I have found on the Internet were broken, so here is my contribution: /** * Returns the URL of a hyperlinked cell, if it’s entered with control + k. * Author: @Frederico Schardong based on https://support.google.com/docs/thread/28558721?hl=en&msgid=28927581 and https://github.com/andrebradshaw/utilities/blob/master/google_apps/convertHiddenLinks.gs * Supports ranges */ function linkURL(reference) { var sheet = … Read more
Try replacing Class Data!A2:A4 with A2:A4
This is done using the setFormula for a selected cell. Below is an example of how to do this. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(“B5”); cell.setFormula(“=SUM(B3:B4)”); You can also use setFormulaR1C1 to create R1C1 notation formulas. Example below. var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = … Read more
For some reason (I confess I don’t really know why) a custom formula in conditional formatting does not directly support cross-sheet references. But cross-sheet references are supported INDIRECT-ly: =A1>INDIRECT(“SheetB!A1”) or if you want to compare A1:B10 on SheetA with A1:B10 on SheetB, then use: =A1>INDIRECT(“SheetB!A1:B10”) =A1>INDIRECT(“SheetB!”&CELL(“address”,A1)) applied to range A1:B10.
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
Use get sheet by name on the spreadsheet: var sheet = SpreadsheetApp.getActive().getSheetByName(‘Local’)
Open Tools > Script Editor then paste the following code: function MD5 (input) { var rawHash = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, input); var txtHash=””; for (i = 0; i < rawHash.length; i++) { var hashVal = rawHash[i]; if (hashVal < 0) { hashVal += 256; } if (hashVal.toString(16).length == 1) { txtHash += ‘0’; } txtHash += hashVal.toString(16); … Read more
You might be better off using the Properties Service as you can use these as a kind of persistent global variable. click ‘file > project properties > project properties’ to set a key value, or you can use PropertiesService.getScriptProperties().setProperty(‘mykey’, ‘myvalue’); The data can be retrieved with var myvalue = PropertiesService.getScriptProperties().getProperty(‘mykey’);
A easy way (if you don’t have that many columns) would be to use a literal array and filter out the empty cells with query. Something like this =join(“, “, query({C3:C; D3:D; E3:E; F3:F}, “where Col1 <>””)) Also see cell A1 in the spreadsheet you shared. UPDATED: An alternative (and shorter) way would be to … Read more