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.

Filter by
Sorted by
Tagged with
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 ...
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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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 ...
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, ...
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 ...
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 ...
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-...
-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 | ? ...
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 ...
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 ...
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 ...
-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: ...
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:...
-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 ...
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 ...
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. ...
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 ...
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,"&...
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:...
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 ...
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,...
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 ...
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....
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 ...
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 ...
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 ...
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 ...
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 ...
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, ...
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 ...
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 ...
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), "...
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 = ...
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 ...
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 ...
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")...
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 ...
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 ...
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 ...
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. ...
-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, ...
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 ...
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 ...

1
2 3 4 5
175