Tag: Sheets

Google Sheets – replace search with areas that are no longer available

I am working on a Google Sheet with several peers. I just have to search for my lines, e.g. For example, lines 1:38 and 197: 222. (It is not permitted to sort the data.) I see many examples on the Internet of how this is done over several sheets, e.g.

 Sheet1!K37:K68;Sheet2!K37:K68

I thought for my data that the following would work:

 Sheet1!K37:K38;Sheet1!K197:K208

No, "Invalid area". Next tried:

 Sheet1!K37:K38;K197:K208

Again no joy. I went back on the internet and did Google and SE searches, but couldn't find an example that met my needs. That should be really easy. What am i missing

Thank you,
– DK

The If Else statement cannot be executed based on the value of the edited dropdown selection in Google Sheets Script

I have a drop down box on a sheet (made of wheat, wood, stone, etc.). When this dropdown box is edited, one of the following similar functions should be performed based on the dropdown selection (for example, if the dropdown selection is wheat, select from area X). I have a function that seems to log exactly which cell the dropdown is in, the previous value, and the updated value. But now that I have an if else statement for each selection, it seems to have conflicting values ​​in the log.

I think either I am not setting the value correctly as a string or I am incorrectly instantiating the variables so that they are one thing for parts of the method – and something else for different parts? Here is the code below as it is current

function onEdit (evt) {

  var selection = evt.value;
  var range = evt.range;

  Logger.log("Hex Changed from: " + evt.oldValue + " to : " + selection);

    if (selection = "Brick") {
    Logger.log("It was " + selection);
    Logger.log("The Paste Area is from row " +(range.getRow()+2) + " and column " +(range.getColumn()-1) + " to row " + (range.getRow()+6) + " and column " + (range.getColumn()+1));
  } else if (selection = "Gold") {
    Logger.log("It was " + selection);
    Logger.log("The Paste Area is from row " +(range.getRow()+2) + " and column " +(range.getColumn()-1) + " to row " + (range.getRow()+6) + " and column " + (range.getColumn()+1));
  } else if (selection = "Sheep") {
    Logger.log("It was " + selection);
    Logger.log("The Paste Area is from row " +(range.getRow()+2) + " and column " +(range.getColumn()-1) + " to row " + (range.getRow()+6) + " and column " + (range.getColumn()+1));
  } else if (selection = "Stone") {
    Logger.log("It was " + selection);
    Logger.log("The Paste Area is from row " +(range.getRow()+2) + " and column " +(range.getColumn()-1) + " to row " + (range.getRow()+6) + " and column " + (range.getColumn()+1));
  } else if (selection = "Wheat") {
    Logger.log("It was " + selection);
    Logger.log("The Paste Area is from row " +(range.getRow()+2) + " and column " +(range.getColumn()-1) + " to row " + (range.getRow()+6) + " and column " + (range.getColumn()+1));
  } else if (selection = "Wood") {
    Logger.log("It was " + selection);
    Logger.log("The Paste Area is from row " +(range.getRow()+2) + " and column " +(range.getColumn()-1) + " to row " + (range.getRow()+6) + " and column " + (range.getColumn()+1));
  } else {
    Logger.log("Something went wrong");
  }
}

The problem is that the & # 39; selection & # 39; value in the if statements is ALWAYS "Brick" even if the selection in the first part of the log was correctly identified as the drop-down list for which it was edited (see screenshot below).

So my question is, how can I get the code to run so that if it correctly chooses what the updated "selection" variable is (now happening), it will execute the correct if statement (not happening now)?

Script log

Google Sheets – Instead, split a column into multiple columns with TRUE / FALSE values

Here's a very small version of what I want to accomplish but can't figure out exactly what to look for on Google –

Column A | Column B
Alice    |  Present
Bob      |  Absent

Should translate into –

          Present    Absent
Alice      True
Bob                   True

So basically every unique value in column B should become a new column and the intersection of the row and column should be True.

Further expansion –
It's more complicated, but is that achievable? –

Convert this –

Column A |  HashTag 1 | HashTag 2 | HashTag 3
Alice    |   corona   | staySafe  | 
Bob      |   lucky    | corona    |  blessed

to

          corona    staySafe   lucky   blessed 
Alice      True       True
Bob        True                 True    True

Here all HashTag columns have the same weight, but each tag is in a different column.

Thank you in advance.

Why can typing in the Google Sheets formula field write over text from time to time as if the "Insert Keyboard" feature was enabled when it isn't?

In the past few weeks, while working on different worksheets on different desktop computers, I have noticed that when entering the formula field, the text is randomly overwritten at about 10% of the keystrokes, as if the insert key had been briefly switched on. Can't find out why. Thanks a lot

Google Sheets – App Script to Random Range with 10% of the total data

I have a table with 20 rows of data.
I want to randomize the data to 10% of the total data.
The script I made is as follows:

function shuffleSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange('A2:D21');
  range.setValues(shuffleArray(range.getValues()));    
}    

function shuffleArray(array) {
  var i, j, temp;
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array(i);
    array(i) = array(j);
    array(j) = temp;
  }
  return array;
}

After I run the script, the data is randomized all the data.
Enter the image description here

The result of what I want can see it below:
Enter the image description here

Google Sheets – Sum and calculate percentages of cells with specific values

I'm trying to develop a marker sheet that returns percentages of cells checked, but discards any unactivated cells.

So far I've done the checked cells – I've given the checked cells a value of 10 and the non-activated cells a value of 0 (I have 10 questions. If someone does everything right, they'll get a score of 100). I try to write 3 formulas that only take into account the activated cells, except everything that is not marked. The formulas I want are:

  1. Total (= total)
  2. Average (= average)
  3. Median

I'm pretty sure I'm & # 39; if & # 39; (e.g. = sumif) or filter (e.g. sum (filter …)), but I have no idea of ​​the exact syntax. Any help would be appreciated.

How can I create a registration report with CSV data in Google Sheets?

I use an LMS platform that can generate registration reports daily and weekly. I share these reports with our participants, but the data is presented in a very messy way.

If possible, I want to have a Google Sheets feature that can scan the CSV and tell me whether a particular student has signed up on a particular day. Is there anything that could do that? I tried using a nested if and query function but it doesn't work.

Google Sheets – How to link a script I created in my PC browser to my mobile app

I did a script, thanks to Tedinoz here.

I have now also created a draw button and added the script

This button appears in my mobile app, but when I press it, I can only delete it so the script doesn't run. There is a way to link for the button to work in my mobile app (no mobile browser?

My working script from the PC browser and the button as it appears in the mobile app are in the screenshot

Enter the image description here