3

You want to update the value in Cells A1 and B4 at the same time. The solution is to update the values as an array. In this script: var values = range.getValues();: gets the entire range A1:B4 values[0][0]= "Apple" ; and values[3][1] = "Banana";: update the array for the equivalent of cells A1 and B4 respectively. range.setValues(values);: updates the ...


2

You have the basic concept right just piece it together step by step. This does the job: =REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR( REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE), "https://example.com/(\S+)"),""))),"\s+"," ") You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...


2

Determine column alphabet letter as follows: =char(64+MATCH(N,1:1,0)) where you substitute the number you want for N=4,5...10. Then add that to the query string formulaically. Done: =QUERY(A1:D4,"select "&char(64+MATCH(5,1:1,0))&" where A = 'Speed'") Important limitation for generalisations: this only works for column index up to 27, because it ...


2

At this time it's not possible by using Google Apps Script or the Google Drive API. Reference Provide read/write access to comments in Google Sheets Related questions in Stack Overflow Getting comments from range in Google Script Count the number of comments on a particular worksheet Creating anchored comments programmatically in Google Docs


2

Instead of =IF(E3<>"",COUNTA($E$3:E3),"") use =IF(ISBLANK(E3),COUNTA($E$3:E3),) Explanations "" is an empty text value, COUNTA counts it as a value. ISBLANK returns TRUE if the reference is blank (meaning it hasn't any value at all). It's similar to nullin some programming languages but not exactly the same. Google Sheets, as well as other ...


2

I just deleted the extra rows and columns in a few worksheets and it worked! It frees up the cells.


2

You can use the following formula: =SORT(B2:B11,ROW(B2:B11)*N(B2:B11<>""),0) Functions used: SORT (Sorts the rows of a given array or range by the values in one or more columns.) ROW (Returns the row number of a specified cell.) N (Returns the argument provided as a number.)


2

Try this Code function onEdit(e) { if(e.range.columnStart === 5 && e.range.rowStart > 2) { if(e.source.getActiveSheet().getName() === "Trends") { var oSet = e.range.offset(0, 6); if(!e.value || typeof e.value === 'object') { oSet.clear({validationsOnly: true}); } else { oSet.clearContent(); ...


1

I think the relative and absolute reference would help you with this. There are 3 types of them: $A1 Allows the row reference to change, but not the column reference. A$1 Allows the column reference to change, but not the row reference. $A$1 Allows neither the column nor the row reference to change. In your case, I think you only want the column ...


1

This should do the trick: and(today()>start_date,today()<end_date)


1

You can split text using menu Google Sheets option Data - Split text to columns: On next step select Separator: Custom Result:


1

Based off the accepted answer I created utility functions to work with ranges (and also named ranges since that's how I'm specifying the cells): This function setNamedRangeValues takes the sheet containing the named ranges, an array of strings which are the names of the ranges, and an array of values that you want to put into each range. The rangeName and ...


1

There is no way to set the order in which the projects will be loaded. If you want to keep the menus in the same order all the time, they should be handled from the same project and from one on open function. NOTE: I didn't tried this yet but I'm pretty sure that it should work Example function onOpen(e){ var menu1 = SpreadsheetApp.getUi().createMenu('...


1

Formula =ArrayFormula(SPLIT(REPLACE(IFERROR(ROW(1:5)/0,)&A1:A5&","&JOIN(",",TRANSPOSE(A1:A5)),ROW(1:5)*2,2,""),",")) How it works The core logic is to create a transposed copy of the values of A1:A5 for each element of A1:A5 then remove the first element for the first copy , the second element of the second copy, and so on, then join A1:A5 ...


1

When getting #VALUE! error in script calling getValue() on a cell that refers to something like value("$3,212.45"), the problem can be a mis-matched locale. To fix this, we need this in the script: var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); spreadSheet.setSpreadsheetLocale('en-US'); and set the locale to the United States in the ...


1

Most of the Google Sheets formulas are calculated on the client-side (the user computer) while some formulas like IMPORTXML and Google Apps Script are ran in the server-side (Google's data-centers). Errors like #VALUE could occurs because the script is reading a value from the spreadsheet before the recalculation (including the transportation time and UI ...


1

=SORT(A1:A3,ROW(A1:A3),FALSE) You can just use sort row, make it false to descending.


1


1

Tl;Dr Try =IMAGE(url) and if that doesn't work as you need it use Google Apps Script and the Google Maps Static API to insert the map as an OverGridImage object. Few weeks ago Google announced that now is possible to insert an image into a cell. Below is the insert image into a cell dialog: I don't think that it could be used to automatically update an ...


1

You can try =switch : =SWITCH(A1:A10,"A",0,"B",1,"C",2) Result : Same result with regexmatch : =ArrayFormula( if(REGEXMATCH(A1:A10,"A"),0, if(REGEXMATCH(A1:A10,"B"),1, if(REGEXMATCH(A1:A10,"C"),2,"no match"))) )


1

in order to have Google Spreadsheets reevaluate a formula, the input arguments to the formula must get new values. In your case, the only input parameter is argument is the name of a sheet, so the formula won't be evaluated as long as the sheet name stays the same (unless you specify a refresh interval in the spreadsheet's settings). But you want to ...


1

In this case I believe the FILTER function would be a much better fit than the QUERY (which becomes quite convoluted). Please try the following formula: =FILTER(A1:I,A1:I1="Adam") Pro Tip Two significant differences of FILTER vs QUERY is that using the FILTER function you get to "transfer/preserve" both the numbers' original formatting as well as ...


1

You can use your original QUERY function. You just have to change like to matches along with the TEXTJOIN function. It is matches that allows you to use a regular expression (regex) in the QUERY function. =QUERY(B14:B,"select B where B matches '.*["& TEXTJOIN(,true,A14:A16) &"].*'") UPDATE following OP's comment: ...make words which only ...


1

You can use the filter function with REGEXMATCH as a parameter, try this in C1 : =FILTER(B2:B100,arrayformula(REGEXMATCH(B2:B100,TEXTJOIN("|",true,A2:A4)))) It filter the range B2:B100 based on which result returns the regex Explanation : The following formula returns False or True based on the content in the cells B2:B100. The operator | is basically a ...


1

There isn't a built-in feature to protect or edit the protection of several sheets at once but Google Apps Script could be used to automated this task or an add-on created by yourself or by someone else. Reference Use Add-ons & Apps Script Extending Google Sheets Related Script to add protection and remove the user running it?


1

Use the concatenation operator & and IF Example =QUERY({ 'ALPHA'!A6:X70; 'BETA'!A6:X70; 'CHARLIE'!A6:X69 },"select * where Col1 <>''" &IF(FALSE," order by Col24 DESC",)) Explanation The formula syntax doesn't include something similar to the PHP # comment, JavaScript // comment, /* */ multiple line comment or HTML <!-- -->, but you ...


1

In your case I think VLOOKUP is a better fit. Actually you can use either of the following two formulas: Either #1: =VLOOKUP(F3,{B2:C6,A2:A6},3,1) or #2: =VLOOKUP(F3,{B2:B6,A2:A6},2,1) As you understand from the second formula we can completely omit column C.


1

It was a syntax issue, my data range was incorrect.


1

I added the custom function: function getRangeAddress(namedRange) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(namedRange); return range.getA1Notation(); } and to use it, I did =getRangeAddress(WidgetTypes) and it returns C5:M5 as a string.


1

I ran into this same issue. I was unable to find a suitable solution to the problem for myself, however I did find a workaround that may work for you. I am working on a project that requires protecting the spreadsheet except for select cells, where end users should not have the ability to edit protected cells without exception. This means strict ...


Only top voted, non community-wiki answers of a minimum length are eligible