Questions tagged [google-sheets]
For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.
0
votes
1answer
8 views
Google Query, change select columns based on cell reference?
I am trying to improve a manual process that requires filtering through the input from a Google Form. It would be great if we could filter each question individually, without building multiple queries....
0
votes
0answers
6 views
Google Sheets not processing very large numbers
I have a Google Sheet I setup for my clan, and we need to put in very large numbers. We are already using the Scientific Notation for the numbers, but one very large number is not getting treated the ...
0
votes
0answers
10 views
Calculate Percentage with QUERY on Google Sheets
I have a Google Sheets database like this:
Name | Value | Answer
A | 100 | Y
B | 200 | N
C | 10 | Y
A | 100 | N
A | 1000 | Y
B |...
0
votes
0answers
14 views
Dialogue Box Input w/o click (just enter) in Google Sheets
I want to be able to use a barcode scanner to enter data in a dialogue box in Google Sheets. The barcode scanner inserts "enter" after the numbers. How can I make the enter trigger the data entry (...
0
votes
0answers
6 views
Can I hide the dropdown arrows from Data Validation in Google Sheets?
I use Data Validation with List from a range set up on all my rows in a column to be able to choose a "Category"
The arrows however, are annoying to see - especially on cells I haven't yet filled.
...
0
votes
0answers
7 views
Recalculation of references after Copy in Google SpreadSheet
I have the problem of the lost references after a copy in Google Spreadsheet that has been solved in that post with the "Find and Replace" of "=". But is there anyway to do that trick in recalculation ...
0
votes
1answer
10 views
How to use 3 separate sheet specific onEdit formulas?
So I have 3 separate sheets in on Google Sheet Doc, I have a script running on the first sheet that hides a row when a drop down is ammended:
var SHEET = "Tweaks Needed";
var VALUE = "All Tweaks Made"...
-1
votes
0answers
10 views
Google Sheet using dialogue box as interface to collect data AND open again for next data
I'm building an electronic sign-in sheet for students at my School Library.
I'd like students to
enter student number (using barcode), username, or last/first
receive feedback that it worked by ...
0
votes
0answers
9 views
Is there a way to conditionally delete columns in Google Spreadsheet?
I have a large spreadsheet with hundreds of columns. About half of the column headings have the word "alone" in them. I need to delete all of the columns that contain "alone" in the header row. Is ...
0
votes
1answer
20 views
How can I use a script across multiple pages of a Google Sheet
I'll start by saying I'm very new to this. Here's my problem. I have a GoogleSheet I created to use as a seating chart in my classes. It is designed so that when I click the blue check button the cell ...
0
votes
1answer
10 views
Google Sheets: Freeze vs Protect?
I have a sheet (screenshot attached) . The first 3 rows are header rows and are frozen . I noticed that a formula that I'd put into U3 in the header
=ARRAYFORMULA({"days since";IFERROR(IF(V4:V*1<&...
0
votes
1answer
19 views
Replacing Query output string when the count is less than 3
Given the reference table below:
Date Location
11/29/2018 Office A
11/29/2018 Office B
11/27/2018 Office C
11/27/2018 Office A
11/27/2018 Office A
11/24/2018 Office C
11/24/2018 Office ...
0
votes
0answers
15 views
Google Sheets Import Price from Website
Could anyone help please?, I would like to extract the price from the following webpage?
https://www.bricklink.com/v2/catalog/catalogitem.page?M=sw822#T=P
Ideally looking at returning the Min,Avg &...
0
votes
1answer
10 views
Google Sheets get row number of visible row
I want to get the row number of the first visible row on one of my sheets. So for example if rows 1 - 499 were hidden then I want to write a formula that returns the numerical value 500.
I know this ...
-2
votes
0answers
12 views
Including insignificant zeroes at the beginning of numbers of varying length
I am trying to scan numbers into google sheets spreadsheet, but some of these numbers begin with a zero. Currently, my spreadsheet will automatically delete the zeroes, but I want to keep them. Plain ...
0
votes
0answers
12 views
Change an entire google sheets to UPPER case, not with an add-on or onEdit(e)
I need to change all the text in a google sheets to upper case, the problem is it's filled in by Zapier which doesn't register as an edit for onEdit functions. It's also automatically converted to ....
1
vote
0answers
14 views
Force Google Sheets to round up to specific fractions
I'm trying to format a google sheet to calculate the amount of raw lumber I need to purchase for a given project.
Raw lumber is sold in quarters, so for example 4/4 is a one inch board, 5/4 is an ...
0
votes
1answer
19 views
Vlookup using Regex Extract - Google Sheets
I trying to do insert an image into a cell in google sheets.
I'm using vlookup and regex extract to lookup columns to retrieve the image URL for the IMAGE function in google sheets.
However one of ...
0
votes
2answers
15 views
How to use an ArrayFormula containing a partial range
I have a Google Sheet that is populated by a linked Google Form. I've added some extra columns to calculate extra values for each row that gets added. I want these columns to be automatically ...
0
votes
1answer
12 views
How to navigate to a specific sheet in Google Sheets based upon a cell value
What I am hoping to do is take the =Now() date that is located on my default sheet and in the Row 1 Column 1 cell and somehow when the sheet is accessed onOpen() have it automatically change the ...
0
votes
0answers
5 views
Count weeks a cell has remained the same
What would be the simplest way to count how many weeks a cell has remained the same? When it is changed I would like the streak to reset to 0 weeks.
0
votes
0answers
12 views
Query Google Spreadsheets select Min and Max dates as Column
I have a large row data sheet with several dates by company, and I need to calculate at column level the leadtime (max date - min date).
Raw Data Source:
--------------------------------------------...
0
votes
1answer
15 views
Add numbers to results of Google Sheets query
Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each records returned from a query.
0
votes
1answer
18 views
Indexing in Google Sheets
I wanted to find a way to add a string into the middle of emails in Google sheets. For example:
We have 5 rows:
bus@email.com
car@email.com
truck@email.com
train@email.com
plane@email.com
I ...
0
votes
0answers
12 views
Google Sheets Put “PASS” in one cell if another cell is green / “FAIL” if red
I have Google Sheet 1 with a result in a cell that will turn green or red based on conditional statements that evaluate if the result is good or bad. On sheet 2 I would like to have post PASS or FAIL ...
0
votes
1answer
16 views
Google Sheets - stretch formula down column, but reuse input from same cell
Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.
On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks ...
0
votes
1answer
17 views
If I set the school to “School A” then only School A's classes should be visible
I have created a progress tracker for the Academy Trust I work in. The trust at the moment consist of 4 schools. Students move between the schools as the progress and between classes during the year ...
0
votes
0answers
9 views
How do I move a value and lock it?
I am looking for a way to take a value from one cell, and move it to another, and the cell that it moves to does not change until a replacement value is put in. Sounds simple, but the value I am ...
0
votes
0answers
11 views
Flow and Google Sheets automation inserting data stating at a certain cell?
I have set up a form for a user to request a WiFi account through Google Forms and Flow. I already have two columns set up as username and password for all the default accounts I need to add the data ...
0
votes
1answer
26 views
Creating a script for Google Sheets to send an email if cells in a row contain a certain value
I have a Google spreadsheet that I created to track daily equipment inspections. The data in the sheet comes from a form that is filled out by whoever inspects the equipment for that day.
Here's a ...
0
votes
0answers
13 views
Google sheets, make checking one checkbox uncheck another
I would like to make it so that if, in a row, column A is checked checking column B will uncheck it, and vice versa. For what I'm trying to do "Neither" is acceptable, "Or" is acceptable, but "And" is ...
0
votes
0answers
9 views
How can I add a check box in Google Sheets based on the content of another cell?
I have a list of four options in a cell. Three of them require "approval" so I need a checkbox in the cell beside it. For one option, I do not want the checkbox to appear. Is this possible?
1
vote
1answer
24 views
Sum upwards until another column's value is met
I found a handful solutions for summing down, but nothing for summing up.
Example table:
type | value
A | -
B | 5
C | 10
D | 7
A | [SUM1 = 7+10+5 = 22]
B | 4
B | 2
C | 3
C ...
0
votes
0answers
6 views
Auto insert column on every Monday of week
The columns should add automatically on every Monday with the updated date of every week. Please let me know how to create that.
0
votes
1answer
10 views
How to reference a Google Sheet by filename?
How to reference a Google Sheet by filename?
I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename.
Example filename:
2018-11.xlsx
2018-12.xlsx
0
votes
0answers
13 views
How to parse custom date format in Google Sheets?
I often have sheets with dates written in different formats than the one GS recognizes as a date. What's a painless way to convert those to dates, perhaps by specifying a format string?
So to clarify,...
0
votes
1answer
14 views
Add n columns to Google Sheet
I have seen How to add N more columns? and I'm not satisfied with this solution.
Isn't there a way to specify how many columns to insert above/below a specified column? Why do I need to manually ...
0
votes
0answers
12 views
Linking Data from one Cell to Another Cell
I am trying to create a spreadsheet that has a list of names on the left and has data associated with the name in the same row to the right. I want to link the data in the cells to the right with the ...
0
votes
0answers
11 views
Blocking cell after changing it's color
I have created simple formula in google sheets which is going to be shared with many people. Sheet will be secured except few columns. I want to let those people edit cell's color in those columns. ...
0
votes
1answer
8 views
Working with multiple If statements, data validation and multiple sheets in Google Sheets
I'm working on a spreadsheet and could really use some help. I'm trying to populate cells in a main worksheet with cells from another worksheet within the spreadsheet.
Currently I'm planning on ...
0
votes
1answer
12 views
Splitting multi-line cells into rows
I'd like to be able to split multi-lined cells into rows using only 1 command.
I have tried using the command
=transpose(split(A2," "))
but when trying to add the 'tab' key as a custom separator ...
0
votes
0answers
11 views
Google Apps Script to Copy data from Sheet1 and Paste in Shee2 Specific Columns
I am trying to copy paste data from Google sheet1 to sheet2. I can copy data from specific cells but I am not able to paste data into specific sheet2 range. My function is automatically pasting data ...
0
votes
0answers
16 views
Import Android phone SMS into Google Sheets
I would like to import some filtered SMS from my Android phone to Google Sheets.
It could be done:
regularly (i.e. every 20 minutes),
event based, when an sms is arriving and matches one or more ...
0
votes
0answers
8 views
Automatically import a range from a different Google Sheet at HOURLY intervals
In a google sheet, I have a dynamically changing cell that updates production every few minutes.
Each hour, I want to pull the new number to my alternative sheet, so that I may get the total ...
0
votes
1answer
19 views
How do I reference a string from a cell in a Google Spreadsheet formula?
I have a spreadsheet that I use to track progress completion based on the percentage of checkboxes that are TRUE (checked).
The formula I use for this has 3 separate references to another sheet. I do ...
0
votes
0answers
6 views
Have column B clear itself if column A's value increase past set intervals
I have little to no experience with scripts, just start using Google Sheets. That being said I don't know if what I want is possible. I have a data sheet that keeps a continuous counting check using a ...
0
votes
0answers
15 views
Is there a way that I can flag in Google Sheets when multiple cells contain the same values?
I'm wondering if someone can help me in my battle with Google Sheets. I'm creating a rota and I want it to flag if all senior members of staff are either off or all on the same shift (either Early or ...
0
votes
0answers
6 views
Seeking formula for multiple columns where one=true and all others default to false
I have a Google Spreadsheet with check boxes in four adjacent columns. I want to drive the user to select one check box (of their choice) and have the others default to "false". The formula below ...
0
votes
0answers
21 views
Button that Creates New Row and Copies Functions/Format
I am attempting to script a button, that when pressed, will create a new row below the currently active cell and copy all of the formats and formulas from the row of the cell that is selected.
I was ...
0
votes
0answers
15 views
How do I interleave rows of data in Google Sheets or Excel
I have data on several different worksheets and I want to combine into single sheet. The data is text, not numeric. I can do this work in either Google Sheets or Excel.
I have:
Sheet1 R1
Sheet1 ...
