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 data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
8,718
questions
0
votes
1
answer
26
views
Scrape blockchain data into Google Sheets
I’m trying to scrape some data in Google Sheets for the following page: https://mtvscout.com/token/0xa4b775e2563fa3a7f4d82ada9595383edc4bcb68/instance/20/token-transfers
The bit of information I need ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Keeping the totals row always visible in Google Sheets
I have data in rows 1:69 and created a totals row 70 that summarizes the above rows. How do I keep row 70 always visible? In the Sheets iOS app, I can freeze rows 1:69 and always see row 70, but in a ...
- 13.2k
1
vote
1
answer
30
views
Use of Countifs to check values from two different columns to match a criteria
I have a data with 3 tabs. Tab 1 has information to be analyzed in tab 3. So in tab 1 I have column J and K which have responses of either "yes" or "No" in one row. So if for ...
CommunityBot
- 1
14
votes
3
answers
48k
views
Is it possible to freeze multiple separate rows in Google Sheets?
Currently, Google Sheets can be used to "freeze" sections of a document so that those sections always persist when scrolling, as demonstrated in this video. However, a limitation of this is that only ...
- 1
0
votes
0
answers
9
views
Google Sheets QUERY to return if contains multiple selection and all other values
I have a data set similar to the image in cells A5:D31. I am trying to write a QUERY function that will return the records that match the "filters" in range A2:D3.
In this example, the query ...
- 19
1
vote
1
answer
20
views
dropdown menu with multiple cells
I'm figuring out how I can use a dropdown menu that will auto fill other cells with the right information. For example i made a nutrition calculator but I'm tired of filling in by hand every time. ...
CommunityBot
- 1
0
votes
1
answer
40
views
Help adding checkbox to google sheets
I am a novice to google app scripts. However I need to copy selected rows (with a checkbox) to a second sheet. I am using this code below. However it copies the entire source sheet. I only need rows ...
CommunityBot
- 1
1
vote
1
answer
78
views
AND / OR Conditional Formatting for Google Sheets with multiple criteria
I am trying to write a custom formula to conditional format column E by referring to the different tenure level targets in column D to indicate if they are hitting their targets base off of their ...
CommunityBot
- 1
0
votes
2
answers
37
views
Making two OnEdit triggers work simultaneously
I'll admit I'm not amazing at code but I can generally survive the basic stuff. At the moment I'm trying to get two onEdit triggers to work at the same time, but I'm struggling (generally only one ...
- 13.2k
3
votes
1
answer
2k
views
Simultaneously sort multiple ranges on a single sheet in varying orders
I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
CommunityBot
- 1
0
votes
1
answer
16
views
How to make arrayformula automatically applied to newly inserted rows?
The following arrayformula needs to be applied to the entire column B, so that it populates Twitter account IDs in the cells of Column B according to hyperlinks in Column A. It firstly checks whether ...
- 13.2k
0
votes
1
answer
233
views
Google sheets -How to modify a number in a formula based on user input
I was trying to automate a project where I would need to modify a number in one of the formula every time as per the user input.
For example: in the above formula, the highlighted number (9) should ...
CommunityBot
- 1
0
votes
2
answers
26
views
I need to extract strings from a cell in google sheets.. I'm trying to use the rexectextrac function, but strings are separated with ""
This is an example of the information i get when reading a QR
{"ver":1,"date":"2021-07-07","name":123456,"line":12)}
What I need to extract is
2021-...
CommunityBot
- 1
-1
votes
1
answer
56
views
Choose an employee and show the days where he has a specific shift code , along with the employees with the same shift
For convenience,
Let's assume we have a shift schedule of a week.
Header has the days from Monday to Sunday, and the 1st column contains the names of the employees.
Names
Monday
Tuesday
Wednesday
...
0
votes
2
answers
25
views
Identify first appearance of data in an unsorted list in Google Sheets
I have a funky lookup situation, consider this example data
Kris | Apr-2022 | ? |
Kris | May-2023 | ? |
Kris | Jun-2023 | ? |
Pat | May-2023 | ? |
Pat | Jun-2022 | ? ...
- 3,306
0
votes
2
answers
738
views
How to export CSV without commas in unpopulated cells?
I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website.
However, when I go to export, it seems ...
CommunityBot
- 1
1
vote
1
answer
14
views
Trigger script to share calender with new user whenever form updates sheet with a new email address
I have a Google form that allows people to submit their name and email address in order to sign up for a private shared calendar.
The form populates a Google sheet.
I want to automatically add new ...
- 3,306
0
votes
1
answer
127
views
Sorting multiple columns using formula
Give a sequence of columns, I want to sort each column individually using formula because of some reason.
Eg.
A
B
C
D
4
7
2
5
1
8
1
3
9
3
4
7
4
Expected output
A
B
C
D
1
7
1
3
4
7
2
4
8
...
CommunityBot
- 1
-1
votes
2
answers
49
views
Formula that calculates the difference between numbers
I'm trying to create a formula that would be able to calculate the difference between the numbers and writes it exactly as shown in this image: https://i.postimg.cc/W4cZDgD2/image.png
Full Example: ...
- 3,306
0
votes
1
answer
406
views
How to insert a note in a cell that has a comment in Google Sheets?
When I go to "Insert note" into a cell that has a comment, the popup for the note either isn't shown, or it's shown briefly and disappears. The comment seems to be blocking it.
I tried both:...
CommunityBot
- 1
-2
votes
1
answer
110
views
Create a dropdown list that only shows items marked as unused! [closed]
I created a table with the list of clients and the licenses they use in different apps, themes, scripts. And these registered licenses appear in a dropdown list so that I can select, but I would like ...
- 44.6k
1
vote
1
answer
2k
views
How do you interpolate data in a horizontal range that's non-linear?
I've been told what I'm hoping to do is called 'spline interpolation'.
I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
CommunityBot
- 1
1
vote
2
answers
192
views
Query formula to add a column in Month/Year format from existing date values
I have a data set where 1 of the columns contains dates in "YYYY-MM-DD" format. I'd like to use a single query formula to select some columns from that data set, including the date column. ...
CommunityBot
- 1
0
votes
1
answer
34
views
Pulling all data from a column
tl:dr - I have a code that can work but want to make it better by pulling everything from column E into 1 message (including header)
I am trying to get the output of a singular column to post to ...
CommunityBot
- 1
1
vote
1
answer
23
views
Countifs in between ranges
I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5.
=COUNTIFS(U13,"1-5")
=COUNTIFS(K2:K22,"&...
CommunityBot
- 1
0
votes
1
answer
24
views
Filtering by flight times in Google Sheets
I have flight delays ranging from hours ahead of schedule to delays of all durations. I need to countif any delay that is longer than 20 minutes. The values in the column take on this format: "-0:...
CommunityBot
- 1
1
vote
2
answers
154
views
Function to search columns and tally data
I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example...
M4 holds the ...
CommunityBot
- 1
1
vote
3
answers
32
views
How would I SUM column values while excluding any that precede the text string "reset"?
I have a spreadsheet where the hours of wear for pieces of equipment are logged:
Data is logged in new rows as additional wear-hours are put on the equipment.
From time to time, equipment is replaced,...
CommunityBot
- 1
0
votes
2
answers
57
views
Nesting IF AND funtions in Google Sheets
I have 3 drop down boxes that I want one cell to choose an answer for based on the selections for each drop down. I can use the IF AND function to produce the one scenario of the result I want but I ...
CommunityBot
- 1
1
vote
1
answer
3k
views
Query based on column header in Google Sheets
I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful.
I have a sheet like this:
in row 1 dates and below songs used that Sunday....
CommunityBot
- 1
2
votes
1
answer
371
views
When I insert a row, indirect does not update
I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
CommunityBot
- 1
0
votes
1
answer
412
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 ...
CommunityBot
- 1
1
vote
1
answer
87
views
How to auto-update cell border color based on how long ago the cell was last updated in Google Sheets?
It doesn't seem that conditional formatting is able to achieve what I'm trying to do. I'm looking for a way to make a range of cells to each auto-update its own border color based on the duration of ...
CommunityBot
- 1
0
votes
2
answers
328
views
Move existing SUM downward when IMPORTRANGE expands
I'm building a complex Google Sheets document and for one sheet I use the function IMPORTRANGE. It's very handy and imports new rows from another sheet when they are added. The problem is this:
After ...
CommunityBot
- 1
0
votes
1
answer
394
views
What is the correct syntax for using NOT in a google sheets filter / match?
My filter function is almost there, but not quite. I want to display only those rows in List1 one that are not in List2, based on the email column (c). This works without the NOT and it displays only ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Average value using different sheets on Google Sheets
I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
CommunityBot
- 1
0
votes
1
answer
56
views
What does SUMPRODUCT do in the following formula?
I am sorry for a noob question, but I am just learning.
Here there is a tutorial to find Last Occurrence of a value. SUMPRODUCT is used for arrays, but MAX in the formula below will return only one ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
CommunityBot
- 1
0
votes
1
answer
20
views
Google Sheets: Creating CUSTOM RULE in conditional formatting to search column contents and highlight row
I'm looking to find one word inside cells in column E with multiple words, and when it's found format that whole row.
I am so lost...
This is as close as I got =$E201=REGEXMATCH(LOWER(E:E), "...
CommunityBot
- 1
0
votes
1
answer
37
views
Remove protection from a specific cell when contents are deleted
I am trying to remove the protection from a specific cell after its content is deleted and then change its background to white.
Here is the script:
function onEdit(e) {
var spreadsheet = ...
CommunityBot
- 1
0
votes
1
answer
82
views
How to filter an spreadsheet using filters + drop down feature in google sheets
I've created an spreadsheet and I'd like to filter the information in the rows using a drop down filter. I've managed to create the drop down menu in A3 but now I'd like to show the information in the ...
CommunityBot
- 1
2
votes
1
answer
16
views
How to do autoresizecolumns() in a Google Sheets file when data imported from another Google Sheets file is changed
I'm new to Google Sheets and apps scripts. This is my first ever question. I apologize in advance if I don't get it quite right.
Google Sheets file Staff Roster sheet Roster contains names, contact ...
- 3,306
1
vote
2
answers
159
views
Formula parse error on importrange query
I'm trying to recreate what's happening in this video at 7:17
https://youtu.be/5S7laJS9meU?t=437
I want to merge two sheets and remove the empty rows.
=QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20")...
CommunityBot
- 1
2
votes
2
answers
400
views
How do I prevent or omit a negative value when calculating between times?
I've researched through the site to see if this has been answered but with no luck after reading:
How do I calculate a time delta in my Google Spreadsheets?
Calculating time difference
Calculating ...
CommunityBot
- 1
2
votes
2
answers
170
views
How to make sequential list of months?
I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
CommunityBot
- 1
1
vote
1
answer
45
views
How do I create a graph that shows progression over time?
Firstly, to help explain what I'm looking for. We're a charity delivering services each week to children and young people. At the end of our service, a volunteer will fill out a report form on each ...
CommunityBot
- 1
0
votes
1
answer
48
views
Google Sheets formula
I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email.
I have 3 companies, and they have slightly different email formats. ...
CommunityBot
- 1
-1
votes
0
answers
608
views
Add row to Google Sheets and keep formulas from previous row [closed]
I want to add a row to my spreadsheet but keep the formulas – and only the formulas – of the previous row. The #1 solution provided here doesn't help me because it also copies non-formula information, ...
CommunityBot
- 1
0
votes
1
answer
68
views
Rounding Negative Numbers in G Sheets
Simple question, is there a function that forces Sheets to round negative numbers mathematically? Presently it round based on absolute value, meaning that -1.5 rounds to -2.0, which is wrong. I want ...
CommunityBot
- 1
0
votes
1
answer
27
views
How to make a query using 2 variables/inputs from 2 different drop down lists
I have a sheet of data that I've been using query functions in. It has worked great so far for single variables, but now I'm trying to make a query from 2 variables, referencing 2 different drop-down ...
CommunityBot
- 1