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.

Filter by
Sorted by
Tagged with
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&"\"&...

1
2 3 4 5
127