excel – In google sheets i want certain range of cells to be locked if checkbox is ticked

In google sheets i want certain range of cells to be locked if checkbox is ticked. for example in column D i have check boxes inserted. If the editors after entering details in row 1 of A,B,C and tick check box in D then the cells A1 to D1 should be locked. Is there any formula or script ??
Also is there any way to protect certain range using script. And what really does this below code do?

        // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
        // permission comes from a group, the script throws an exception upon removing the group.
        var me = Session.getEffectiveUser();
        protection.addEditor(me);
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        } 

google sheets – Conditional formatting if cell text matches one of multiple other checked cells texts

Finally, after literally 8+ hours of trying around, I found a solution.

First I filter all the Recipes that are checked with in cell I3

=FILTER(B3:B,C3:C=TRUE).

Then I use 2 conditional formatting formulas:

Forumla for A1:A

=AND(countif($I$3:$I,B3)>0,B3<>"")

Formula for D3:G7

=AND(countif($I$3:$I,D3)>0,D3<>"")

For anyone wondering, I use AND to include a second rule (D3<>””) to exclude blank cells from highlighting.

Can’t believe it was so easy..

This is how it looks now:
enter image description here

Frontend – Is there a way to disable or enable text wrapping in cells?

I am trying to generate "Program" Cells, but my problem is different "Code" Cells, "Program" Cells automatically start wrapping text when the line is too long for the screen. Is there an option or something to do this "Program" Cells have no text wrap, so very long lines of code may disappear from the screen, but remain on the same line as "Code" Cells.

Here is an example "Program" Cell:

long=StringJoin@Table[".",1000];
CellPrint@Cell[TextData[{
    StyleBox["Code:",FontWeight->"Bold"],"   ",
    " ... very long piece of code "<>long,"n",
    StyleBox["Comment:",FontWeight->"Bold"],
    " ... very long comment ......"<>long
}], "Program"]

Here is an example "Code" Cell:

CellPrint@Cell[TextData[{
    StyleBox["Code:",FontWeight->"Bold"],"   ",
    " ... very long piece of code "<>long,"n",
    StyleBox["Comment:",FontWeight->"Bold"],
    " ... very long comment ......"<>long
}], "Code"]

google sheets – Merged cells autofilling

I have a sheet with merged cells in a column. Merged cells are of different sizes (not the same number of rows). I want to autofill this column, for example by typing “1” in the first one, then extend it down and get 2, 3, 4… But if tried that, the size of the first merged cell is also extended and all my cells get the same size… Which is not what I expect.

I also tried using a formula “=max(A$1:A1)+1” in the cell A2 and then using Ctrl+Enter to copy it in all the cells but the same issue arise : my merged cells get resized…

So my question is : is there an easy way to autofill merged cells of different size in Google Sheet ?

google sheets query – Macro to copy and paste values from multiple cells as text into a Note

I’m new here and struggling a bit with the javascript in macros. I just need a steer as to how to get a macro to copy from a range relative to an active cell.

I’m a showcaller, that is, during live events I tell technicians when to play videos or music or to change lights etc.

I use a Google Sheet for my running order and each line contains the various cues – actions that technicians must take, like ‘play Video 1’ – separated into columns for the different departments: Lights, Sound, Video and so on.

I want to gather the cues for different departments as text in a Note in a single cell. I can then edit the text to write my standbys – the warnings of upcoming cues – and read them from the Note during the event.

So I am trying to make a macro to take the contents of the cells in a relative range and paste them together as text in a Note in a single cell.

I’ve tried recording a macro and using cut-and-paste to select a range and put it into a Note on the active cell. However this only placed the contents of the clipboard into the Note as text – it didn’t do the cut-and-paste as part of the recorded actions in the macro.

I’m happy to do the work to write it – can anyone help me, please, with the correct way to have the macro copy and paste from a relative range. Thank you.

How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D

I’d like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside this link)

I’ve tried the formula =IMPORTRANGE(“1jD9MgFRnIi-44GaqsS5XAdhcV-Vg36eXnAXzkFh5xnM”,”‘Form Responses 1’!C2:C”) in the new spreadsheet and it populates C, however, I only want it to show D IF/WHEN someone types their Username (from C in the original sheet) into Cell B3 in the new sheet (see image 2) … If that makes sense.

P.s. I also only want two rows and two cells in the “new spreadsheet” (also see image 2). One Column for the search box (cell B3) where they type their username, and the other for the content in D (cell C3) that corresponds to that username in C.

i.e. D is autofilled when C is typed in

enter image description here

enter image description here

google sheets – How do I get a formula copied to all the cells below it in the entire column (e.g. 900’000 cells) instead of manually dragging it down to eternity?

If I have a formula, =IF(MOD(ROW(),1)=0,A2+1,A2) in Cell A2, how do I get it copied to all the cells below in the entire column (e.g. 900’000 cells) instead of manually dragging it down to eternity?

Also would like for this formula to be applied to the entire column, =ArrayFormula(IFERROR(VLOOKUP(B2:B,$A$2:$D,3,0))) , from cell E2.

Use countblank to count the number of empty merged cells in Google Sheets

I am trying to find the number of empty merged cells in a range, for example the 6-cell range of A1:F1. If the cells weren't merged, I could just use them =COUNTBLANK(A1:F1), but let's say A1:C1 be brought together and D1:F1 are also merged. If I have a value in A1:C1, =COUNTBLANK(A1:F1) is issued 5 instead of the one you want 1.

I am working on a Google Sheets worksheet that has been heavily formatted by the designer to allow other data to be output in the table. The design uses a lot of merged cells and unfortunately, removing them is not an option.

How can you do that?

Formulas – Column for the conditional format of Google Sheets with cells that contain more than one value

I have a google sheet, it is as follows:
Col. E.

  • 3 days keto plan 250g / vegetarian
  • 5 days keto plan 400g / normal
  • 5 days keto plan 400g / organic
  • 3 days keto plan 250g / normal
  • 7 days normal plan 250g / breakfast
    etc. etc.

I want to be able to color this column using a conditional format, and the criterion is:

  1. if the cell contains "Keto" AND "250 g" cell color = orange
  2. if the cell contains "Keto" AND "400 g" cell color = green

Also in another column H there are terms like 400 g standard, 250 g standard, 400 g … etc. I want to be able to stain cells in column J based on the term in column H. If H contains "400 g", the cell color in J is red. etc. (only fit for 400g, not the stuff afterwards)

Data structure – How is grid-based collision detection handled when an entity spans multiple grid cells?

Thank you for your response to Game Development Stack Exchange!

  • Please be sure answer the question. Provide details and share your research!

But avoid

  • Ask for help, clarify, or respond to other answers.
  • Make statements based on opinions; Support them with references or personal experiences.

Use MathJax to format equations. MathJax reference.

For more information, see our tips on writing great answers.