In a QR code, how much would the proportion of black cells have to vary from 50% for us to conclude that it’s probably (p

Generally speaking most QR codes look as though they have about 50% of their cells black. If a putative QR code has 90% or only 10% of its cells black, we would reasonably conclude that it doesn’t look like a QR code. How much would the proportion of black cells normally have to differ from 50% before we can reach such a conclusion using a criterion of p < 0.05?

How to select multiple individual cells on Google Sheets on my Android mobile phone?

I use this feature all the time…

Having opened your sheet on Android, simply tap the cell you wish to access

enter image description hereand at the bottom of the screen you will see the place where you can Enter text or formula. Once done, tap the tick ✓ (right of screen at the end of the entry panel)

enter image description here

and then tap the tick ✓ at the top left hand side of the screen and the job is done.

enter image description here

You can only enter data in one cell at at time but if you are wishing to select a number of cells (for example to edit colours, formatting cell ranges etc), use the corners of the cell selected and drag to open a range (but you cannot enter data that way nor can you, for that matter, in a browser. Neither can you you cannot select multiple unconnected cells.

How to select multiple individual cells in Google Sheets Android app on mobile phone?

How to select multiple individual cells in Google Sheets Android app on mobile phone?

On Windows it is possible by using the CTRL button + left mouse button and select multiple cells, but I did not find any solution on Google Sheets Android app.

How to select individual cells on Google Sheets on my Android mobile phone?

How to select individual cells on Google Sheets on my Android mobile phone?

On Windows it is possible by using the CTRL button + left mouse button click, but I did not find any solution on Google Sheets for Android.

Google Sheets: Link/Sync Two Cells Dynamically (based on a third column’s data)

I want to preface this by saying this is NOT a duplicate of Sync two cells value. It’s similar, and uses the OnEdit() Function of Google Sheets, but the solution presented in that thread only works statically. This is an extension of that issue, not the same issue.

Let’s act like I work for Twitch for a second. My company uses a GSheet to keep track of all of our Monitored Streamers for the day, which includes regular streamers, partnered streams, and third-party pulls from other sources which require manual interaction. Each row includes the date, time, the name of the streamer, the stream ID, and the status – On Air, Delayed, Done.

Now, we keep the “Pulls” group on a separate subsheet, since those require actual effort on our part to go on air on time, as opposed to the normal streams which go off automatically. The “Pulls” subsheet is similar to the Monitored Streamers subsheet in that it has the date, time, name, and status of each Pull stream, but it also includes the encoder information on the same row, hence the need for a separate sheet.

Now, the way we have this done currently is that the status can only be changed from the main “Monitored Streamers” sheet, but the status is reflected in the appropriate box in “Pulls”. (Done with this function: =IF(E9="","",IF(COUNTIF('Monitored Streamers'!E:E,E9)=0,"Not Found",VLOOKUP(E9,'Monitored Streamers'!E:G,3,FALSE))), which from what I understand finds the stream ID in column E then grabs the status from col G, and prints “Not Found” if it can’t find the stream ID on the MS sheet.)

This is kind of awkward, since it means that when I – someone who only works with pull broadcasts – want to mark a stream as “on air”, I have to copy the stream ID from the “Pulls” sheet, go to the “Monitored Streamers” sheet, find the stream in question, mark it there, and then go all the way back to the “Pulls” sheet and repeat the process. It would be much easier if I were able to just update the status on the “Pulls” sheet and have it affect the “Monitored Streamers” sheet automatically, without disrupting the ability to change the status of the “Monitored Streamers” sheet directly.

Now, I read through Sync two cells value, but as I stated before, the answer for that thread only works on static cell placement and hardcoded sheets. Our rows move constantly, since we’re always adding and removing streams from the sheet, so this solution won’t work.

Could someone please either write a script or teach me how to write a script that links the cells in column on subsheet 1 and subsheet 2, iff the data in column on S1 matches column on S2? It would be awesome if I could edit what columns it looks for too, since we change the number of columns in each subsheet on a regular basis.

EDIT:

Actually, now that I’m reading the linked topic again, I’m realizing that what I’m really asking for is a version of that script that has abstractions of the cell ranges as opposed to hardcoding the whole thing, preferably through function parameters so we can link multiple different cells across both sheets. (Sometimes we have to change the stream ID or date and whatnot, and it would be awesome if all of those synchronized across both sheets.)

Thing is, I don’t know the first thing about GApps scripting, so I don’t know how to do that myself.

Why does Google Sheets Conditional Formatting not apply properly to all cells?

Alright, so I’ve been having this issue for a few hours now and am confident to say that I can’t fix it by myself.

I’ve been playing around with Google Sheets trying to apply Conditional Formatting when another referenced cell is not empty.

Here’s an image of the issue, it will definitely help you understand what I’m trying to do.

However, here’s what happens when I do the following two things (using an IF function to determine the state and then parse the true or false state towards the Conditional Formatting rule), here’s where the broken magic comes into play.

When I set it to =IF(ISBLANK(B2), false, true), this happens.
But when I reverse it and set it to =IF(ISBLANK(B2), true, false), this happens again.

I am lost to why this happens as from what you can see in the screenshots, B2 is never empty.

EDIT: For whatever reason, when I set it to =IF(ISBLANK(B2), true, true), it works as expected, as seen here. I would still like to hear an explanation if anybody has one.

How to programmatically Evaluate Notebook (all cells in an open notebook) rather than from Evaluation menu?

I am working on a DockedCells toolbar for common tasks like Save, ClearAll, scrolling, etc., etc. My next addition to that toolbar is creating a button that executes Evaluation -> Evaluate Notebook. I only need the function to work. I already know how to insert that into a button.

When I type NotebookEvaluate(EvaluationNotebook(), InsertResults -> True)directly into a cell and doing a Shift-Enter nothing visual happens other than showing (Running...) for an extended period of time at the top window frame of the notebook. I am not sure what is supposed to happen here so I did an Abort Evaluation as too much time has passed. It should only take 1 second for this particular notebook.

Why isn’t this behaving like doing Evaluation -> Evaluate Notebook from the menu?

Automatically update inserted sheets cells in Google Docs

This answer is based on the work of user79865.

You have a Google Form which exports results to a Google Sheet; you have embeded the sheet in a Google Doc and you are looking for a way to update the Google Doc automatically whenever there is a new form response.

Your question is substantially answered in webapps question: Embed Google Spreadsheet table Withing Google Document when data contains dates which in turn was based on this webapps question Embed Google Spreadsheet in Google Document.

Your scenario differs in two ways:

1 – you want to update the Google document for every new form response, and
2 – you want the update to be automatic.


Installation

You have a Google Form, Google Spreadsheet and a Google Document.

  • Google Document
    • make a note of the document ID
  • Google Form
    • select your spreadsheet as the response destination
  • Google Spreadsheet
    • Open the sheet’s Script Editor, rename the project as appropriate, and copy the script shown below. You can rename the script if you wish.
    • var sheetName = "<<insert sheet name>>";: edit the script to include the actual name of the sheet containing the form responses – (put the sheet name between the quotes)
    • var docid = "<<insert document ID>>";: edit the script to include the actual document ID – (put the ID between the quotes)
    • save the modified script
    • Trigger: from the menu, select “Edit, Current Projects Triggers”. In the new tab that opens
      • Under “Choose which function to run”, select the name of the function.
      • Under “Select event type”, select “On form submit”
      • Save the trigger

Processing

  • When a form submission is made, the spreadsheet will be updated.
  • the trigger will execute the function.
  • the function looks for a named range called “embeddedSheet-range” (you can change this if you wish).
  • when the function executes for the first time, the range isn’t found, so the function creates the range and inserts the table.
  • when the functions executes thereafter, it finds the named range and replaces the it with the updated data.
  • the function will replace any existing data with new data

function wa14658402() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "<<insert sheet name>>";   // name of sheet to use
  var sheet = ss.getSheetByName(sheetName);
  
  //get the last r and column of data
  var sheetLR = sheet.getLastRow();
  var sheetLC = sheet.getLastColumn();
  
  // define the range containing the data
  // this can be changed depending on which columns to embed
  var range = sheet.getRange(1, 1, sheetLR, sheetLC);
  // Logger.log("DEBUG: the range is "+range.getA1Notation()); 
  
  // note - this is a change mentioned in 
  // Embed Google Spreadsheet table Withing Google Document when data contains dates
  // when the data includes a data, "getValues()" fails hence
  // changing the method to "getDisplayValues()"
  var values = range.getDisplayValues(); 
  
  // get the document details
  var docid = "<<insert document ID>>";
  var doc = DocumentApp.openById(docid);
  
  var body = doc.getBody();
  var ranges = doc.getNamedRanges('embeddedSheet-range');
  
  if (ranges.length == 0) {
    //  Logger.log("DEBUG: ranges is zero")
    var table = body.appendTable(values);
  }
  else {
    // Logger.log("DEBUG: ranges is NOT zero")
    tableRange = ranges(0);
    table = tableRange.getRange().getRangeElements()(0).getElement();
    var ind = body.getChildIndex(table);
    tableRange.remove();
    body.removeChild(table);
    table = body.insertTable(ind, values);
  }
  var rangeBuilder = doc.newRange();
  rangeBuilder.addElement(table);
  doc.addNamedRange('embeddedSheet-range', rangeBuilder.build());
}

In Google Sheets: How to check if a Date is within the Date range of two other cells

I have a sheet where column A shows one month per row and column B is supposed to calculate an amount in USD for that month.

What I am trying to achieve is that the USD values in column B are automatically calculated by looking at start and end dates specified in two other columns and then taking a corresponding value from a column next to the date range if the month in column A falls in between that range.

This is tricky to explain so I have created a demo which you can look at here: https://docs.google.com/spreadsheets/d/1vJkkEkNdLBIH6ifx_B7FJLiL9azJHyIuSRy90IRfPp4/edit?usp=sharing

I have found a few suggestions for similar problems online but wasn’t able to get any of them to work for my specific case. Any help is very welcome

google sheets – copy color theme from data validation cells

So I’m using data validations alot.

for better organization I create tab called setup and I put my list of possible options there

let’s say I want to categorize my credit card expenses

so I’ll put a possible categories list in the setup tab as following:

enter image description here

now in the other tab: credit card expenses, I’ll set data validation rule as such:
enter image description here

(*) I usually select longer list than the existing one so I can easily add more later and it will already be defined in the list (for this example I can add more in cells L8:L17)

Now, to make things more readable I like to add colors,
So I want that the following color theme will be applied in my credit card expenses:

enter image description here

Now, as far as I know, if I want to set such color theme, for N categories,

I need to set N different conditional formatting rules stating:

if X==”supermarket” => color=yellow

if X==”entertainment” => color=purple

etc..

Now, as you can now see, if N is very big, this process gets annoying,
especially if I have multiple tabs such as credit card expenses and I need to set those rules to each of them..
Also if I want to change those colors later, I’ll need to update those rules everywhere I did so.

Also, cutting and pasting cells kinda ruins conditional formatting rules so I don’t quite like them..

so my question is:

Can I simply color up the cells in my setup tab and somehow set the
data validation rule to copy the color with it from the range list?