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.
6,813
questions
0
votes
0answers
6 views
How to add arrayformula function to this formula with IF function?
I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
0
votes
0answers
3 views
How to change focus from one cell to a different row and cell
I'm trying to make an ID Card Tracking List in Google Sheets. Example: B5 would contain the issuance date. Once filled in the cell is no longer accessible, focus switches to C5, the expiration date. ...
0
votes
0answers
8 views
Import data from a different file but only if it matches some of the data in the new one?
I would like to copy information entered from another Google Sheets file but only if it relates to the information in the new file.
For example, MR SMITH's employee number is entered into one sheet ...
0
votes
1answer
9 views
Range returning null using getRange
I am new to Google Apps Script and the docs are a little fuzzy on the returned data from a method call. I want to loop through a range, but it is coming back as null. What am I doing wrong?
var ...
0
votes
0answers
10 views
Formula to Have Sheet Populate Data From Another Sheet If Referenced Cell Exists in Import Sheet
I am doing my best to write formulas, but I am still super new to it all. I am doing my best to figure out how they work and I am stuck at the moment.
I am looking for a formula that can reference ...
0
votes
1answer
5 views
problems with form and appendRow
I created a form in Google Script and one of the fields is a Checkbox, the name of the field would be field1 []
and to save the answers in a sheet, I am using AppendRow, but when I insert the name of ...
0
votes
1answer
17 views
How to Import Data From Another Sheet If Any Data is Present In A Cell Range Corresponding to The Same Row
I am extremely basic in my understanding of coding formulas for Google Sheets, so please help where you can. The formula I'd like to put together is to search a vertical range (column) of information ...
0
votes
1answer
5 views
Google Apps Script MailApp Not Sending Email To Script Owner
I've set up a script to take information from a Google Form and email that information to multiple people using Google Apps Script. What it does not do, however, is email the owner of the script. I'...
0
votes
1answer
11 views
Multiply on a Specific Date (investment simulation for students)
I am trying to create an investment simulation for my high school students in Google Sheets. They already have a bank account that they use (in google sheets) for reward for specific behaviors. I ...
0
votes
0answers
5 views
Dynamic Line Charts for Stock Performance Overtime
I've been riding the struggle bus for a few days now trying to figure out dynamic charts.
I am trying to create a dynamic chart that will show stock data for specific increments of time. (30, 60, 90 ...
0
votes
1answer
7 views
Google Sheets Google Forms updates
I have created a Google Form for which collects data from a user. Once the use submits the form I have scripts that copy the data to several other Google sheets and then send an email to the ...
0
votes
2answers
16 views
Dynamically form a cell value in Google Sheets
This might be very trivial, but I was not able to obtain any concrete result from my Google searches, so asking here.
I want to fill a column with the content of some other column (in some other sheet)...
1
vote
1answer
19 views
Using a custom formula on a sheet that is constantly expanding
A custom formula for 5 possible colors, by day is used to highlight entries by DayOfWeek
Is it possible to use a format like A2:A instead of manually editing it as the sheet grows bigger?
Formula ...
0
votes
0answers
29 views
Google Document from a Google Sheet [closed]
Suppose I have a Google Sheet with each row corresponding to a film, and columns corresponding to Title, Director, Nation, Year, Cast, Plot etc. Having sorted and filtered the sheet in some way of ...
0
votes
1answer
22 views
Can a publicly shared file stored in my Google Drive be deleted by someone having “Anyone with the link” editor rights?
Following a concern expressed by a user at How to share a Google Sheets demo spreadsheet.
The problem with sharing it editable is that anyone can trash the sheet. Surely it's better just to share it ...
0
votes
1answer
10 views
Send email when cell value equals 29 [duplicate]
I am having trouble with my google sheets script and I wondered if anyone had any advice for a beginner. I want my script to check a cell value (Sheet1, H2), then send an email if the value = 29. ...
-1
votes
1answer
16 views
Automatic Invoice Number
Need some help with making my invoice number automatically go up by 1 once I have hit the button to save the invoice in Google Spreadsheets.
0
votes
2answers
18 views
Function/formula to return only the first string proceeding the first carriage return
Is there a function/formula to return only the first string proceeding the first carriage return using Google Sheets?
Example Text:
Bob & Mary Smith
bob@email.com
555-111-1234
Assuming ...
0
votes
2answers
21 views
Searching Multiple Columns From Single Source Sheet in Google Sheets
I have a Sheet where I have all of my data entered. I have been able to filter the information to other sheets no issues when only searching in one column. I am having issues when trying to search 2 ...
-1
votes
0answers
24 views
Can i import specific external map route data to regularly update output in cells? [closed]
I am attempting to use ~real-time maps routing data times to keep various drive-times updated to real-world changes in traffic, in a Google Sheet.
My MAP requirements:
Route DURATION data ...
0
votes
1answer
10 views
How can I use a filter to show everything except cells containing specific words?
I'm importing a Google Calendar (ics link in A2 that I'm importing with an IMPORTDATA function) and it has system dates related to 1970 that I obviously want to filter.
How can I specify to show me ...
0
votes
2answers
29 views
How to stop banding from re-appearing on Google Sheet
I'm using banding to highlight some areas, with a section in between with a fixed colour. It should look like this.
Sometimes, when I enter a number in the blue area in the middle, the banding ...
-1
votes
0answers
10 views
“Publish to the web” not working with ImportJSON when original file is closed [closed]
I'm unable to reliably share a sheet from Google Sheets with "Publish to the web" reliably, since it displays zeros as values instead of the values I have in the original sheet.
I have a ...
0
votes
1answer
11 views
I need to extract a data validated cell content and use it as a part of a new formula
I would like to give the user the option to choose between DAYS360() and NETWORKDAYS().
For that, I thought about adding these 2 to a data validation cell.
Afterwards, I would like to be able to add ...
0
votes
1answer
11 views
How to use multiple criteria with Regexreplace in Google sheet?
I am trying to correct the number/expression of room numbers that are entered by users with the following:
1- If the number contains ( / or \ or _ ), it will be replaced with "-".
2- If the ...
0
votes
0answers
12 views
how to remove contact from Google contacts when email removed from google sheet using apps script
I have an Apps script when run:
reads Google sheet,
adds new contact/s ( name and email) into Google Contacts,
a duplicate is prevented to be entered into Google contacts as well.
However, I want when ...
0
votes
0answers
13 views
How to use Divide function actively? Ex: If I change the value of divider, how do I keep this up to date?
How do I make the values of divided, sum etc. active? I can't figure this out.
If I use the =DIVIDE(etc). the values in these cells stay the same if I change the values of divided or divider.
0
votes
0answers
15 views
Google Sheet: calculation/math inaccuracy [duplicate]
I found weird math error. I was doing calculation that comes down to this:
=TRUNC(253-0.81*300) This comes back as 9, but should be 10. For some reason, when you increase the visible decimal places, ...
0
votes
1answer
30 views
Plotting gigantic values + smaller values on the same Google Sheets chart?
I'm trying to plot a chart that includes one data set in billions, another in millions, and a third in thousands.
What is the most efficient/elegant way to do this using google sheets without the ...
0
votes
1answer
9 views
typing 1 to 9 on keyboard does not trigger editing cell in Google spreadsheet
When on a iPad in Google Sheets and typing 1,2,3,4,5,6,7,8 or 9 does not trigger the editing of a cell.
0 and all other characters do.
Is this a new feature/bug in google sheets or can I change a ...
0
votes
0answers
8 views
How to show all formulas (Sum, Avg, count) in Google Sheets when you select multiple cells [closed]
Currently, when you select multiple cells, only 1 function is shown on the bottom right corner, either SUM or MIN or MAX or COUNT.
How can I make it so that I quickly see all functions at once?
0
votes
1answer
25 views
Add checkboxes in a column automatically when another column contains a value (or not) in Google Sheets [closed]
I need to automatically add checkboxes in column F but only when column G contains a value in the same row.
Conversely, I want a checkbox to disappear or be disabled when the cell in column G in the ...
0
votes
1answer
18 views
How to change IFS function to switch in Google sheets? [duplicate]
I have this formula =ifs(OR(A1="January",$A$1="March",$A$1="May"),$A10,OR($A$1="April",$A$1="June"),$B11,OR($A$1="February"),$C10) I would ...
0
votes
0answers
14 views
How to change IFS function to switch? [closed]
I have this formula
=ifs(OR(A1="January",$A$1="March",$A$1="May"),$A10,OR($A$1="April",$A$1="June"),$B11,OR($A$1="February"),$C10)
I would ...
0
votes
1answer
17 views
Google sheets range of sheets?
I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these ...
-1
votes
1answer
11 views
Problems when sorting a Google sheet that someone else is editing
In the past, when two people were editing the same Google sheet, if person A attempted to do a sort they would get a "heads-up" warning that A's sort was going to affect person B.
We now don'...
0
votes
0answers
14 views
Autofill Google Sheets formula incrementally
I'm looking to pull data from several separate sheets into one overview to have a snapshot picture of a range of booking in my venue.
I'd like to look at the venue booking per space for any given day, ...
0
votes
0answers
12 views
Google sheets - edition inventory [closed]
I need help making a Google Sheets script to streamline our sales inventory. Right now we get a sale and it automatically updates a sheet, we then select that sale and manually copy it's info in ...
0
votes
0answers
16 views
Action paln formula for google sheet
I need to do a plan for my team. Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call, break, and task.
I have to add a rule so that an agent per day ...
0
votes
1answer
9 views
Way to link to first (leftmost) tab in Google Sheet?
I have a Chrome bookmark pointing to a time tracking spreadsheet I have created.
Every month I add a new tab for the new month, which becomes the first (leftmost) tab.
Unfortunately this causes my ...
0
votes
0answers
9 views
Import data from Investing.com or other sites into spreadsheet not possible anymore? [duplicate]
I wanted to import from the section of historic data high/low prices in weekly ranges. For example from this site:
https://www.investing.com/indices/us-30-historical-data
I tried =INDEX(IMPORTHTML(&...
0
votes
1answer
19 views
Formula for Google Sheet
I need to do a plan for my team.
Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call break, and task.
It is a team of 15 people, I can't find any ...
0
votes
1answer
9 views
How to find closest similar value above current value, and return a different column on the same row
I'm looking for a way to find the closest similar value(in distance) above the current value(column B), and return the column next to that found value(column C) in column D.
A sort of vlookup where it ...
0
votes
0answers
5 views
Show label when hovering over point in diagram
My chart is quite crowded but I need to be able to look up the manual labels assigned to each point. Is there a way to make Google spreadsheet show the manual label upon hovering over the point with ...
0
votes
1answer
20 views
Get the last three values of column in Google Sheets
I need to create a report that pulls the last 3 cell values in a column.
This will need to be in three separate cells.
Column C will contain data which will be added to daily.
D2 will contain the ...
0
votes
0answers
22 views
How do you color a cell if no other cell references it? [duplicate]
I am making a spreadsheet with a bunch of variables I want to change and some formulas that use those variables. I want to use conditional formatting (probably?) to color any cells which are "...
0
votes
0answers
7 views
Differences between copy-pasting from Excel/Calc/Google Sheets to Whatsapp [closed]
When I copy an excel table (or libreoffice calc) and paste it on whatsapp web I have a photo but when I paste a Google Sheets table I get the plain text on whatsapp web, as well as the images below
...
0
votes
1answer
55 views
Count of weekdays and the time duration per day
I have a column that has 34 records of Week Day, Month/Day, and Times. I am looking for two formulas that I can use in a table that will give me the count of weekdays and the time duration per day. ...
0
votes
1answer
15 views
Depending on Sheet being edited, get a cell value and trigger OnEdit update
This code works but not as intended. The workbook has several tabs (Sheets) and if the user is editing Tab1, then and only then a trigger OnEdit should execute. If the user is editing some other tab (...
-1
votes
0answers
13 views
Create a formula in Sheet
I want to make a formula to check if the text of a column matches a text added in another in undefined range and without ordering and give as an answer yes or no.
Example:
In column A:
Red wine
White ...
