Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.
7,098
questions
0
votes
1answer
6 views
After deleting cell contents, cell is no longer “blank” when comparing in a conditional using == “”
In my else statement below, if a cell in column G is blank I want to clear the contents (the timestamp) of another cell in column F using the event function onEdit(). However, after testing the code ...
1
vote
0answers
9 views
COUNTIF with function in criterion
I'd like to count all cells in a range where a given function evaluates to TRUE. The documentation for COUNTIF & COUNTIFS show text-matching and evaluation of =,<>,<,>, but nothing ...
0
votes
1answer
13 views
How can I use the onEdit() trigger to timestamp a cell if another cell in the same row is checked “TRUE” in apps script?
I am able to add a timestamp in column 5 using a conditional when I'm comparing the column to a number, but what I am actually trying to do is add a stamp to the corresponding cell in column 5 if a ...
0
votes
0answers
19 views
Google Sheets Query with importrange by date range
Trying this Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/18r5cARjmlRsbb0VRaAS6kAMkJm1p2qe0LsPCtYLS1xA/edit?resourcekey#gid=561906440","Form Responses 1!A:X"),...
0
votes
1answer
14 views
Google Sheet multiple lookup values and return entire row
I'd like to lookup the activation type in C6 as well as the date range found in L6 and O6 on the "dashboard" tab and return the resulting rows (specifically columns E-J) from the "...
0
votes
0answers
8 views
Grouping rows by value templates
Is there any option to automatically group rows by their values if they fit certain criteria or template?
For example I want to group all iOS 14* records into one and all Android 8* into other
Plus ...
0
votes
0answers
12 views
Google Spread Sheet Import Question
I have some Google dorks like (Around 4000+)
"site:uber.com"
site:twitter.com"
What I want is to fetch results/links of each search on Google.
Is there any way?
I add url like that and ...
0
votes
0answers
21 views
Combine multiple Google sheets that can keep updating
Okay, so I think, no one seems to have exactly the same problem as I do...
I have an app writing automatically my health data into a google sheet.
It does so in three different sheets inside the one ...
0
votes
0answers
11 views
Duplicate and rename a sheet (only it`s data) at fixed hr and day
every monday I've to make a copy of a sheet which is called week_nn, where nn is a progressive number of the current week for that sheet from its beginning. At some point of the day, we have to fix ...
0
votes
0answers
10 views
Google Sheet filter issue [duplicate]
I have a Google Sheet that collects responses from a Google Form, this collects contact details and preferred location. I shall call this workbook Feeder.
In a second Google Workbook (called ...
0
votes
2answers
33 views
How can I include two formulas in a single cell in Google Sheets?
I’m in the process of making a spreadsheet of stocks that I’m watching in Google Sheets. I want to include the daily price and percentage change in a single column. I found a helpful Docs Editors ...
-1
votes
0answers
16 views
How can I show historical performance for stocks in Google Sheets?
I’m in the process of making a spreadsheet of stocks that I’m watching in Google Sheets. I want to include columns showing historical performance (1-month, 3-month, etc.) for all the stocks included ...
0
votes
0answers
12 views
Array formula works in the sheet but invalid in conditional formatting
I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible.
I use start1 and ...
0
votes
1answer
17 views
referencing a column in a Google sheets array literal
Is there a way to refer to columns in a literal array like so:
=FILTER({lengthy_arrayformula_1, lengthy_arrayformula_2}, condition referencing column 2 of the array literal in previous argument)
or
=...
0
votes
1answer
11 views
Dealing with data from multiple individual spreadsheets. Should data be merged OR are there better ways to deal with multiple sheets?
I have transactional data (spreadsheets) from different time periods and would like to know if there are better ways to import the data to a mastersheet, than to merge all the spreadsheets into one? ...
0
votes
1answer
18 views
importrange on google shhet
I have a question regarding the importrange() function. This is my formula:
=ImportRange("1u0TjjekVHHb6shYiSWoo0W1ygxuSu2tVvLstnEE1NFo";"Juillet!I5")
I have about 20 rows of data ...
0
votes
1answer
20 views
Applying conditional formatting independently to each other
Here is an example file for where I want to apply conditional formatting independently of each other
First I have highlighted all males in the spreadsheet
Then if they are a senior I want the text of ...
0
votes
1answer
19 views
Google Sheets - How can i collect and add values related to a value next to it in a table and add them to another table
I'm using app sheets to see what I do in a day. I select the task i am starting on the app and app sheets dumps the data into a table in google sheets.
I have made an example of what this looks like. ...
1
vote
0answers
10 views
If there anyway i can check if the cell contains the result of a arrayformula()?
For example i have this in A1 :
=arrayformula(B1:B100)
If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
0
votes
1answer
25 views
Google Sheets - Turning text in cell into an image
I was wondering if it's possible to turn text into a picture. For example the text "Arizona Cardinals" is imported automatically into a Google Sheet and that cell recognizes "Arizona ...
0
votes
1answer
12 views
Need help referencing data from another tab and inserting the result in a single cell
I am having trouble getting this to work in google sheets. I want E2 from tab "Prepack Dashboard" to reference the data in A2 and B2, match those to A2 and B2 in tab "Inventory Minimums&...
-1
votes
0answers
14 views
Clock in sheet time range
Can someone please help with this tried to get time from a range closest 5 minutes:
=IF(NOW()-TODAY()<$J6:$J6,IF(NOW()-TODAY()>$J258,$J258,VLOOKUP(NOW()-TODAY(),IN,1)))
Range is from 06:00 am - ...
0
votes
0answers
4 views
How to delete my Account since I'm NOT allowed to comment I'm out the door so please tell me HOW to delete my Account [migrated]
Yeah, just joined. Don't see a way to EXIT this group where it says I must have 50 to comment when I'm NOT allowed to comment even ONCE. A complete wa$te of precious time so if you know please inform ...
0
votes
0answers
12 views
Google sheets - how can I use functions to resort my table in a way charts can read it well
I'm using app sheets to see what I do in a day. I select the task I am starting on the app and app sheets dumps the data into a table in google sheets.
I have made an example of what this looks like. ...
0
votes
0answers
12 views
Is there a shortcut like doc.new but defaulting to a shared Google Drive instead of My Drive?
I love the handiness of doc.new and sheet.new, but they default to creating documents in My Drive, which I then need to remember to move afterwards. Is there some URL I can bookmark that will create ...
0
votes
1answer
10 views
Send email when there is a change in column
I would like an automatic notification to be sent to several people when I entered 'Yes' in column C. I wish this would apply to Sheet1 only, not to the entire file.
I have this code
function ...
0
votes
1answer
26 views
Importrange internal error in Google Spreadsheet
I am trying to import data from one Google Sheet to another sheet using import range function, my data is not that large to break it only has four column and rows is 2000 than also when am importing ...
0
votes
1answer
11 views
create data validation (pulldown) from other spreadsheet
I have current spreadsheet (named as INPUT). In this INPUT , i have a sheet named USER. I share USER sheet to other users so they can write data on it (as editor). On cell A1 , i will use data ...
0
votes
1answer
14 views
Hide selected cell indicator from other users
When i shared a spreadsheet, i will see that if other users click on a cell , it will be higlighted in my end. I don't want to see this because it's quite annoying.
Is it possible to disable the ...
0
votes
0answers
4 views
Is there a way to make the Google Speradsheet fetch data (ImportXML) wait a longer time to time out?
I use Google Spreadsheet to grab some data from Alexa.com
However, it would time out for some cases.
Is there a way to make it (the ImportXML method) wait longer?
0
votes
1answer
12 views
Unlinking checkboxes
I have 3 checkboxes. When I check one, others get checked too. How to unlink them?
//Validating Permition
if(shUserForm.getRange("C14").getValue() == "0"){
ui.alert("Blank ...
0
votes
0answers
5 views
How to locate & compare script sources across a bunch of copies of the same G. Sheet ? It's messy! [closed]
I'm getting used to coding in Sheets, using their script editor. I've coded a bunch of macros over time on a few projects, but one sheet in particular I keep multiple backups of (the data gets updated ...
0
votes
0answers
16 views
Can I configure MacOS to open CSV and Excel files in Google Sheets? [closed]
Is there a way to have MacOS open CSV and Excel files in Google Sheets when you double-click them? I've tried associating the Google Sheets shortcut in Applications but that doesn't work.
0
votes
0answers
16 views
Google Sheets: Line Graph of alternating columns. 1st Column as label
Month
Notes
January
Notes
February
Net Worth
$500
$600
Liabilities
$50
$40
Credit Cards
$10
$20
Credit Card 1
$5
$10
Credit Cards 2
$5
$10
Savings & Checking
$400
$500
...
0
votes
0answers
5 views
Data Studio formula for one-to-many count
I have a large table of transaction data that I am trying to analyze. The table is formatted with a transaction having multiple rows depending on the number of items purchased (one row for each item).
...
0
votes
1answer
21 views
Return a list if 2 criteria match in Google Sheets
In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have ...
0
votes
1answer
30 views
Grab the right most value using arrayformula
I have this sheet :
https://docs.google.com/spreadsheets/d/163jhtdKcC6Pu34jX9brolZDb5bm-PWqG7i-yAtNueoo/edit?usp=sharing
Trying to get the right most value per row
Basic formula :
=FILTER(B2:2,COLUMN(...
-1
votes
1answer
22 views
How to transpose and split in Google Apps Script?
How do write script for below
google form response as below
time stamp
Document No.
Recipient Name
Document transmittal ref no
7/9/2021 21:36:11
170709, 170738, 171769, 172003, 172025
Susan
20210001
...
0
votes
1answer
13 views
find counta() per row using arrayformula()
I have this table :
https://docs.google.com/spreadsheets/d/1ICEL33X3ADz7XEH2Cmi1p6mrYT2RqPwbtBjCHWoNkoA/edit?usp=sharing
Basically i want to do counta() per row but using arrayformula(). The basic ...
0
votes
1answer
35 views
Google Sheet Filter and Match (List the label where a name exist.)
I have 4 game columns that contain the names of the players. I want to list the games of a particular player.
A. B. C. D
Basketball Bowling Billiards Badminton
--...
0
votes
1answer
25 views
vlookup with multiple output and combining filter() with arrayformula()
I have 2 sheets : shot and deadline.
https://docs.google.com/spreadsheets/d/1drNUQVtAxd0HU4QCVSKJSsKHoanE735n8HsjkYpYiGs/edit?usp=sharing
This is my shot sheet :
and this is the deadline sheet :
So ...
0
votes
1answer
14 views
Google script (custom function) doesn't get updated inside a cell
I have this function :
function clastrow(targetsheet) {
var SS= SpreadsheetApp.getActiveSpreadsheet();
var Sheet = SS.getSheetByName(targetsheet);
return(Sheet.getLastRow());
}
function ...
0
votes
1answer
21 views
Joining two tables on a google sheet
Every day I will download a "product inventory" list from an third party source, the list is in the format like this:
Product Code
Product Name
Quantity
1
Apple
100
2
Pear
200
3
Banana
...
-1
votes
0answers
15 views
Google Sheet formula explanation for stock market
=min(index(query(sort(GOOGLEFINANCE("NSE:SBIN","low",today()-100,100,"WEEKLY"),1,False),"select * limit 10",1),,2))
This is a formula for finding low of last ...
0
votes
1answer
11 views
Can we do arrayformula for repeating horizontal join?
I have this table :
where i want to join all data in each row start from column D to last column. I want the output just like in that C2:C5
I use this formula in C2 :
=JOIN(",",filter(D2:2, ...
2
votes
2answers
147 views
Formula to count the number of different letters in a word
In sheets, suppose cell A1 contains the word CABBAGE.
I want to put a formula into, say, cell B1 that will count the number of different letters. So the output should be 5, because there are 5 ...
0
votes
1answer
24 views
How do I copy cells so that only the sheets where they reference from is changed?
I have a expenses sheet, and other sheets named Jan, Feb, etc. These sheets are identical except for their numerical values.
In the expenses sheet I want to have columns, each of them applying to a ...
0
votes
1answer
23 views
Get the cell address of maximum column and row containing the most right and bottom value
I want to grab the address of the cell of the most right and bottom value. This image should explain it. yellow is the the most right value. and the green is most bottom value. And the orange is the ...
0
votes
1answer
16 views
Count a cell based on if a cell in the same row has a checked checkbox
I want to write a formula to count the Total Cost cell in column F, only if the cell in H is checked. I've approached it a few ways, but the Countifs formula isn't computing it.
0
votes
0answers
9 views
Weighted Average Grade Book Based on Conditional Dropdown
I am trying to create a grade book using Google Sheets that produces a weighted average based on three grade categories; exams=45%, participation=30%, and homework=25%. I would like to do this be ...
