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,306
questions
0
votes
0answers
2 views
How to delete old rows in Google Spreadsheets?
Hi i have a google sheet that collect tweets from twitter.
i need a function that should delete rows that are more then 2 weeks old.
here is the sheet https://docs.google.com/spreadsheets/d/1Zhhl6-...
0
votes
0answers
15 views
Link audio files in Google Drive folder in a Google Sheet
I have hundreds of audio files (.mp3) in a Google Drive folder that I need to list with links in a Google Sheet. Very similar to the problem described here, but I was not able to adapt the code to ....
3
votes
2answers
40 views
How to get a difference of “X years, Y months, Z days” between two dates?
I have two dates: 29/05/2020 and today.
How can I get a date diff of these 2 dates, in a meaningful format such as "Z years, X months, Y days" separating these dates?
Ideally this should ...
-2
votes
1answer
26 views
How to select multiple individual cells on Google Sheets on my Android mobile phone?
How to select multiple individual cells on Google Sheets on my Android mobile phone?
Example:
On Windows it is possible by using the CTRL button + left mouse button click, but I did not find any ...
0
votes
0answers
19 views
Google Sheets: Link/Sync Two Cells Dynamically (based on a third column's data)
I want to preface this by saying this is NOT a duplicate of Sync two cells value. It's similar, and uses the OnEdit() Function of Google Sheets, but the solution presented in that thread only works ...
0
votes
1answer
9 views
weird bug(?) with getValue returning “You are trying to edit a protected cell or object”
This is just...bizarre...
I'm running a function with a user that does have full Edit access to the Sheets in question.
This code:
function test() {
var ss = SpreadsheetApp.getActiveSpreadsheet();...
0
votes
1answer
27 views
Why does Google Sheets Conditional Formatting not apply properly to all cells?
Alright, so I've been having this issue for a few hours now and am confident to say that I can't fix it by myself.
I've been playing around with Google Sheets trying to apply Conditional Formatting ...
0
votes
1answer
20 views
In Google Sheets how do I search for a specific value in a column?
I've started working with Google Sheets.
I'm looking for a value that I believe exists in a specific column.
I know which column the value exists in.
How do I find the value?
1
vote
1answer
27 views
Autofill Google sheets template based on new entries in column A of “master” sheet
I am a teacher and I keep my student data on a Google sheet. I have a "Student Data Report" template that will automatically copy as a new sheet with the student id as the sheet name as I ...
1
vote
0answers
65 views
Google Sheets: How to query until reaching a particular sum?
I have a sheet with a lot of data, but to keep this simple let's say column A has a product, column B has a date, and column C has an integer which can be blank, 1, or an integer greater than 1:
...
0
votes
1answer
18 views
formatting currency to be used in an email
I'm summing a range from a Google Sheet, then putting the results in the body of an email as currency.
But I'm running into formatting issues that seems surprisingly hard to deal with.
The range of ...
0
votes
2answers
25 views
concatenation truncating numberic value
in a cell J21 I have a set value 1.10000.
When using concatenate =CONCATENATE("=CONCATENATE("@ £/€ rate: ",(J21)) in another cell elsewhere on the sheet, I get the result "@ £/€ ...
0
votes
0answers
15 views
Can you define a conditional format range without an absolute reference?
I would like to conditionally format a column in my sheet, regardless of how long the sheet is. This is because new rows are routinely inserted, and the sheet gets longer but the conditional ...
1
vote
1answer
33 views
What is “Open in AppSheet” option?
Today I noticed new option in Tools menu of Google Sheets: "Open in AppSheet".
For one spreadsheet it's inactive:
For other spreadsheet it is active:
What is the purpose of this new option?...
3
votes
1answer
38 views
How do I reference a cell for the sheet name in IMPORTRANGE function in Google Sheets when the sheet is in another workbook?
I have two workbooks with several sheets in each. I have a dropdown to select the sheet name that I'm using for reference for URLs and to reference other sheets in the main book.
I successfully used ...
0
votes
2answers
30 views
How to pull data from one sheet to another
Very inexperienced google sheets user here. I have a sheet with employee data. One row and many columns of data for each employee. Some employees appear more than once (i.e. in multiple rows)
I need a ...
0
votes
0answers
26 views
Removing values in array without filter() in Google Sheets
I want to remove specific (or any) values in dynamic array/range and return the result of the resized array without those values without using filter() as I need to use arrayformula.
For example,
{&...
0
votes
1answer
21 views
How to apply DATEVALUE to column
I have two columns with extracted date values. I want to add those two values together and apply to the whole column. The data looks like this:
AM AN AO AP AQ
date time ...
1
vote
2answers
24 views
Using ARRAYFORMULA extensively in complex formulas
I have a formula which looks like this:
=ARRAYFORMULA(IF(A4:A = "", , IF(AND(ISNUMBER(I4), ISNUMBER(J4), ISNUMBER(K4), ISNUMBER(L4)), SUM(I4:L4), "???")))
The idea is that for ...
0
votes
2answers
34 views
Virtual Teacher Trying to Automate Attendance
I am a virtual math teacher with almost 200 middle school students (I love my "mute all" button!). We were just told we need to start keeping attendance in the school's attendance program, ...
0
votes
1answer
15 views
How do I use text and a formula in a cell
"Books Added in October 2020 " & "counta(A3:A)"
This is not working.
Can someone help me?
0
votes
1answer
25 views
When setting a duration, hide the hours?
What I needed for a spreadsheets is specifically times "mm:ss.ms" for example: 00:30.522 (which is 1823 frames @59.7275fps). If possible, 30.522, but that's not a big deal. I was able to get ...
0
votes
1answer
30 views
Sheets - Custom Data Validation Combobox
I am having a problem with making my own custom data validation combobox, here is what I am having a problem with:
Sheet (Hardware) Column A Column S:
Column A = Asset Tag
Column S = is Owned? Yes/No
...
0
votes
1answer
16 views
Google Sheets interpret numbers with one decimal point as date
With locale set to Brazil (number format is 24.999,75 and date is 20/5/2017) typing a number with a point separator (.) results in different interpretations depending on the number digits after the ...
0
votes
0answers
27 views
Query all Google Sheets data from Google Drive?
How can I query all Dates from all Google Sheets in my Google Drive?
As far as I know, I can do multiple query for all the sheets using importrange, however, I have over 100 Google Sheets:
={query(...
0
votes
0answers
33 views
How to prevent editors from re-naming a document in google sheets?
I am the owner of the document made in Google Sheets. I need editors to be able to edit the contents of the document but I do not want them to be able to re-name it. I am currently experiencing this ...
0
votes
1answer
26 views
Stacking IF Statements
I am trying to pull data from another sheet based on selections made from multiple dropdown boxes. For example... If column 1 text reads RAZA pull data, if column 1 reads Standard pull data. So on and ...
-1
votes
2answers
70 views
Is there a maximum number of people who can simultaneously view a public Google Sheet?
I want to publicly share a link to a Google Sheet for viewing only (not editing). Is there a maximum number of people who will be able to view the sheet simultaneously?
This is a Google document on my ...
-1
votes
0answers
27 views
How can I duplicate a Google sheets tab multiple times and retain the protections for each tab?
I found a great script from this community that allowed me to duplicate the tab while retaining the permissions, but I need to do this several times. For example, I need it to duplicate tab '1' 49 ...
0
votes
1answer
36 views
Google Sheet jump to current date on Open (very large sheet)
I understand that this question has been asked before but most users seem to only have the dates in one column or a limited number. The sheet I am trying to work with has the schedule for one entire ...
0
votes
1answer
30 views
If colE contains value then import colAY from same sheet
I am trying to import 2 columns from one sheet into another but trying to make sure I import only the cells from col2 when col1 has a value (all different values).
I imported my first column needed (...
0
votes
1answer
30 views
Script to SUM Values
Can you help me please?
I want a script to sum rows after a name change.
Something like this:
COLUMN A - COLUMN B - COLUMN C - COLUMN D - COLUMN E
aa - aa - 5€ ...
0
votes
1answer
26 views
How to make scientific notation “E” go away in google spreadsheets
https://support.google.com/docs/thread/45190976?hl=en
I tried doing what the above article stated, but it didn't work. Now, to give a clearer picture of what I'm dealing with:
In the first cell, this ...
0
votes
2answers
25 views
How to have a continuous calculation in google spreadsheets=
So, I want to continuously divide something. I have a number, and I want to divide it into a new number, and then divide that new number, and so on. How do I make a google spreadsheet do this?
0
votes
1answer
21 views
Google Web App Vlookup
I'm looking to create a simple Google Web App with a single field that accepts input and uses VLookup to match data in column A and return data in the column B of a Google Sheet.
Can someone point me ...
0
votes
0answers
26 views
Given Columns A and B, how do I group values of column B that have the same value in A?
Here is an example image with an expected result, as you can see Jon and Alice get grouped together as they share the same date (and so do Bob and Carter).
1
vote
0answers
31 views
Google Sheets IMPORTXML shows “”Meet Google Drive“ one day and ”#N/A" the next
I am trying to import a simple xml file into a new Google Sheet, via the importxml function:
importxml("https://drive.google.com/uc?id=1HqnptNBNn3yyjvaEVDNOpEWjegchwMpU", "//")
...
0
votes
0answers
29 views
how to create a link in a google spreadsheet cell to reveal a local file in Mac Finder
I want to be able to click on a link in a google spreadsheet and reveal a local folder / file in finder and I will create multiple links of this kind in one spreadsheet for this purpose. Does anyone ...
0
votes
1answer
35 views
Generate Table from Sheet to HTML Web App using Google App Script
I am working on a web app via Google Script where every time I search for a specific text it will generate a table that I linked in Google Sheet in that Web App.
This is what I have so far:
Google ...
1
vote
1answer
24 views
Using vlookup to return values automatically
So, I work somewhere where we keep a spreadsheet for serial codes for consoles (mainly for warranty purposes). However the system is a bit inefficient so I'm trying to change it a bit.
We use ...
0
votes
1answer
29 views
How to join text in google sheets if it starts with …?
I would like to join cells with text if it starts with "substitute".
Now I know joining the text without a condition creates this formula:
=JOIN(" ";F9:AE9)
However on the y-axis ...
0
votes
0answers
27 views
Making Interval Graphs on Google Sheets for Velocity vs Time [migrated]
I am trying to make a graph like the one pictured above in google sheets for Velocity vs Time. I have the position vs time data and I am trying to get the slopes of two points I already have to be the ...
0
votes
1answer
20 views
Convert weekday to text output
In a monthly spreadsheet I have cells which contain the year (A1) Month (A2) and then the rest of the column running from cell A4 through to A34 numbered as the day in the Month (1 to 31).
My ...
0
votes
1answer
24 views
Use array as function parameters
Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function?
For example, I'd like to pass the result of SPLIT("example.com,...
0
votes
2answers
52 views
How to get DATEVALUE to recognize RSS 2 pubDate (RFC822)?
My formula contains an RFC822 pubDate, e.g. as pulled from Google's RSS2 Feed.
=DATEVALUE("Thu, 01 Oct 2020 10:00:00 +0000")
but it results in
Error
DATEVALUE parameter 'Thu, 01 Oct 2020 10:...
0
votes
0answers
38 views
How to create a three level hierarchical view of relational columns
I've been struggling creating the following view but I'm so close.
I have a dataset with three columns of data where each level is broader than the other
I want to be able to filter the data ...
1
vote
1answer
30 views
Expand Countifs Arrayformula in rows and columns
In this google sheet
I have an array of Types and floors, The criteria rows and headers are auto populated by unique formulas.
I'm trying to create an array formula which will expand in the width (...
0
votes
1answer
21 views
Sort a column by name with query between tabs
I am pulling information from one tab to another and I believe this is why it will not allow me to sort (because it is not sorted in the original tab). The easy solution would be to sort that tab. ...
0
votes
0answers
16 views
copy color theme from data validation cells
So I'm using data validations alot.
for better organization I create tab called setup and I put my list of possible options there
let's say I want to categorize my credit card expenses
so I'll put a ...
0
votes
0answers
46 views
Didn't know how to crosstable and the format column change
I have a vertical table, like this:
I want to query it like this crosstable function:
I have tried ArrayFormula like this:
=ArrayFormula(SPLIT(QUERY(UNIQUE(FLATTEN(IF(LEN(A2:A10&"\"&...
