Tag: Sheets

How can you *turn off* the new “Show Edit History” feature in Google Sheets?

I turned on “Show Edit History” for one cell in a Google Sheet and now I want to turn it off for that one cell.

I can’t see any way to do so. If I right-click on the cell I see “Show Edit History” but it is not a toggle, as I would have hoped. I don’t see anything useful in the menus and web-searching has not been fruitful.

To clarify, I am not trying to turn off or opt-out of the entire feature. As an example. I can “bold” the text in one cell, and then I can “unbold” the text in that same cell. And I can activate “show edit history” for one cell. I want to be able to deactivate “show edit history” for that one cell.

Please help. Thanks

This is related to Is there a quick way to see when a cell in a Google Sheets was last edited? .
I’m not allowed to comment on that.

microsoft excel – Add % sign in Data Labels Google Sheets

You can use a VBA macro for this:

Sub AppendPercent()
  Dim objCell as Range
  For Each objCell in Selection
    If objCell.Value <> "" Then objCell.Value = objCell.Value & "%"
  Next
End Sub

Now before running this macro, select all the cells where percent is needed to be appended. Then click Alt+F1 to open Microsoft Visual Basic for Applications and then in Insert > Module paste the code. Then hit F5 to run macro.

How to combine Excel sheets horizontally (fixed number of rows)?

I have a workbook with dozens of different sheets that have the same structure (see exemplary sheet 1).

What I need to do is combine all sheets into one by “pasting” all columns of all sheets alongside each other such that in the sheet containing all the data I still have N number of rows (i.e. as many rows as there are countries, see screenshot 2 for how I need my resulting sheet to look like).

I would be extremely grateful for any tips!

google sheets – How do I write a script that exits a cell that is being edited

If I enter a value in cell A1 without exiting the cell 1. Before I exit editing A1 I run a script that at some point selects another cell B1 2, while A1 is still being edited. When I then click C1, the value moves along to the cell B1 before C1 is selected 3. That is what I want to prevent. The value should remain in A1 when the script selects B1 and I select C1.

How do I call for the initial state of cell A1 being edited to stop before I select another cell.

Case statements in SWITCH statements in Google Sheets

What you should actually use is the IFS function instead

=IF(LEN(A2),IFS(A2<=60, "F", A2<=70, "D", A2<=80, "C", A2<=90, "B", A2<=100, "A"),"Unknown")

Using the extra function ArrayFormula we apply the same formula to each row for the whole range

=ArrayFormula(IF(LEN(A2:A), 
    IFS(A2:A<=60, "F", A2:A<=70, "D", A2:A<=80, "C", A2:A<=90, "B", A2:A<=100, "A"),""))

enter image description here

google sheets – Search multiple columns for one value

This is roughly what my table looks like:

A            B               C               D               E               F

Name    |Ingredient 1   |Ingredient 2   |Ingredient 3   |Ingredient 4   |Ingredient 5

Dish 1  |flour          |tomato         |potato         |eggs           |wine

Dish 2  |eggs           |onion          |capsicum       |tomato         |pasta

Dish 3  |flour          |eggs           |dill           |chicken        |pork

What I want is to search for the ingredient, and get a result of all the dishes that contain that particular ingredient or even highlight the dish name. Each ingredient is in a different column and not in any sorted order.
E.g. suppose I enter in another cell somewhere (or in another sheet) the word tomato, then all the dishes that contain that ingredient should show up. Is that possible with google sheets? This will be a constantly growing table.

google sheets – Duplicate row values to next row, based off specific column

I’m trying to set up a button that will copy information from the last row and paste it into the row right underneath (based on where column D last has a value). Almost like a “duplicate” button, because I am inputting information that will repeat.

I had a script that was doing the copy perfectly. However, I realized an issue — because I have a unique ID pre-loaded to fill up all rows in the first column (causing the added row to come after this), I wanted this script to seek the last row based on the FOURTH column, not the FIRST.

On a similar pattern, I am looking to ONLY copy the information from column 4, 5, 6, and 7, into the row underneath — not any other info.

My idea was that I could basically combine these two concepts — copying and pasting the last row into the row underneath, and using GetColumnHeight so that I can control which columns should be selected.

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow();
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,4,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});

  var vs=sh.getRange(e.range.rowStart,4,1,3).getValues()
  tsh.getRange(getColumnHeight(3,tsh,e.source)+1,4,1,3).setValues(vs);
    }
  }
function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  var v=sh.getRange(1,col,sh.getLastRow(),1).getValues().map(function(r){return r(0);});
  var s=0;
  var h=0;
  v.forEach(function(e,i){if(e==''){s++;}else{s=0;}h++;});
  return (h-s);
}

I’m really all over the place right now, and struggling to piece something together that will copy and paste the values in column 4,5,6, and 7 into the row below, ignoring the filled values in column A. Any suggestions?

Here is a visual to further explain my goal:

https://docs.google.com/spreadsheets/d/1aUQJr8mdizNR9LHavHHt_K34Yf0ZVI4nl38FD9o-LHo/edit?usp=sharing

Data Scrape to Google Sheets from MyFitnessPal.com

I would like to be able to pull the macronutrient (and more if possible) data from MyFitnessPal using what i assume would need to be an importhtml() or importxml() function directly onto a google sheet row using and specified by the date.

Also I believe that there’s a limit to how many times on one sheet the function can be used so if the above is possible would it be possible, in addition, to pull the data and then insert as value so as new dates get added it doesn’t keep using the import function each time the gsheet is opened

I’ve set my profile to public so I was wondering if there was a way. I tried using importxml() and it seems to pull something

What i’ve found is that
if i do
=IMPORTxml(“https://www.myfitnesspal.com/food/diary/&E1&?date=2020-10-22″,”//a/@href”)

where cell E1 is the username

It does pull in data but it shows the following which is not what i’m looking for.

I’m hoping to at bare minimum get carbs, fat and protein (if possible sat fat, fiber, sodium)
enter image description here

google sheets – Filter causing Circular Reference Error – Not obvious why?

I have 3 sheets. SheetA and SheetB contain data and a third sheet where I want to merge the contents of the SheetA and SheetB in it.

I type this formula in the result sheet (the 3rd sheet):

={filter(SheetA!A1:A, arrayformula(ISBLANK(A:A)=false)),
filter(SheetB!A1:A, arrayformula(ISBLANK(A:A)=false))}

However, I get circular reference error! Not sure what caused it? Each filter in the above expression works correctly.

Link to sheet

Google sheets array formula for taking average every Sunday and output is in same row corresponding column

I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday.

If a cell in B is left blank then it should not be considered for an average. Meaning if on all numbers were filled for 6 days and the 7th was blank then the average should be the sum of the 6 days divided by 6 days.

Without an arrayformula() i was using:

=if(weekday(A8, 1)=1, average(B2:B8),””)

Any help would greatly be appreciated!

For example A8 is a Sunday so C8 should show the average of the range B2:B8

enter image description here