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
31 views

How to force reloading custom function result? [duplicate]

I use this script to get the current sheet name : function sheetName() { return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); } then use =sheetName() in the cell to load the ...
andio's user avatar
  • 355
2 votes
1 answer
35 views

How to set a minimum value for a cell in google sheets?

We use google sheets to do price updates for our websites. Sometimes there may be a typo that allows too low of a value and we want to avoid the possibilty of these kinds of errors. Ideally, looking ...
user321796's user avatar
0 votes
1 answer
25 views

Conditional formatting on individual rows in range

I want conditional formatting with color scale to easily highlight values from lowest to highest within a row. However, I have many rows. If I go for range A1:X50, the formatting will only be based on ...
Morphy's user avatar
  • 3
3 votes
1 answer
41 views

Is there any way to justify text in Google Sheets?

I want to justify text in a cell in Google Sheets. I searched Google and have no clue how because there are only 3 choices of alignment: left, center and right. Do you have any ideas or ways to solve ...
satker pusdatin24's user avatar
1 vote
1 answer
43 views

Automatically fill in the email address of the person that ticks a checkbox

I've created a table of data in Google Sheets. Several rows of data need to be validated by a superior. How would I add a checkbox for those rows, such that whoever ticks them as the verifier would ...
Nadia's user avatar
  • 11
0 votes
1 answer
32 views

Creating Formulas with Loops in Google Sheets that increment Cell References

So, I basically have a lookup sheet that is ever-changing in which I have various IP Addresses that I list in a dropdown list box. Depending on the selection made, I want to automate the lookup for ...
2Advanced's user avatar
3 votes
1 answer
77 views

Google sheets and semi-autopopulation of data for attendance of a private school where students come two days a week and get to choose the day

The students in my school are not all scheduled to attend every day. In Google Sheets I have a sheet, 'student list' that lists the students that attend classes for each given weekday: The first row ...
Steven's user avatar
  • 33
1 vote
1 answer
51 views

Track elapsed hours in Google Sheet

I have a table in Google Sheets that I use to track the time elapsed between a TIME IN and a TIME OUT. As you can see below, my TOTAL formula in D3 works, calculating the elapsed time from 5 AM to 3 ...
ZenLife18's user avatar
0 votes
1 answer
100 views

Change tab color based on cell vaule

I am trying to change the tab color based on a cell value on an individual sheet. I have multiple tabs. The cell value that will determine whether or not it gets a color is a cell containing a date. ...
Vikki DeFilippo's user avatar
0 votes
1 answer
32 views

IMPORTRANGE sum column with condition

=SUM(IMPORTRANGE("1kWMBhk0T423p411K5_5sKOMQFdAsEDapdasYyr9gZj8","Oct!N2:N") "SELECT Col16, where Col16 contains 'Paid' )") I need to calculate only the total of paid ...
user321359's user avatar
1 vote
1 answer
84 views

Stop Google Sheets from removing trailing zeros

Can anyone suggest a Google Sheets formula to stop removing trailing zeros? (but allow truncating to 6 digits after decimal? 00.000000 29.9918650 -93.958005 29.991865 -93.958005 (1mi:29.991865:-...
Most Interesting Man In World's user avatar
1 vote
1 answer
27 views

Importing specific rows within one document to another sheet

I found a similar question on your site. Importing only certain rows between spreadsheet documents Is it possible to realize the same import within one document? I have a table with a large number of ...
K14M's user avatar
  • 23
0 votes
0 answers
32 views

Can I use conditional formatting in google sheets to force cells to merge given a specific value [duplicate]

I am working on formatting a cash log for work (work in a restaurant and right now we record this by hand) the problem because each days entry isn’t uniform as the number of people working changes so ...
Rcyr1010's user avatar
0 votes
1 answer
17 views

Add X in a column corresponding to given name automatically when new row is added to shared sheet

I would like to create something, which will be able to automatically add X in a given column to newly created row in a shared google sheet. The sheet is my work schedule and every row represents a ...
Edward Noga's user avatar
1 vote
1 answer
21 views

Prevent edits to an Apps Script project bound to a Google Sheet [duplicate]

I have a google sheet where I require users to edit certain cells with all other cells locked and with a tick to reset script button, but I would like to know if its possible to lock the script from ...
DZDU's user avatar
  • 11
0 votes
0 answers
24 views

Is there a way to invoke the expression in a cell (or the data structure it yields), rather than its value?

Someone provided me with a complex formula, that I want to understand and improve. I started by breaking it apart to see what each part of it yielded, and then chaining the parts to rebuild the whole. ...
Daniele Procida's user avatar
2 votes
1 answer
167 views

LAMBDA or ARRAYFORMULA with GOOGLEFINANCE

Working on a formula in Google Sheets to return True/False based on searching through an array of data using: =TO_DATE(INDEX(SORT( GOOGLEFINANCE( A2,"high",TODAY()-365, ...
RTG's user avatar
  • 23
1 vote
1 answer
28 views

How to use Google Script to email a pdf of just one sheet (tab) within a spreadsheet/

I have located some similar posts but none of the solutions seemed to work. I have a function that sends me an email PDF of a spreadsheet. I just want the email attachment to send the first sheet (tab)...
Darren's user avatar
  • 11
0 votes
1 answer
25 views

Return only those rows with a date on or before today using Query and Importrange

In Google Sheets I have a QUERY where I want to import only those rows where the dates in Col23 are on or before today's date. This is my current formula: =QUERY( IMPORTRANGE( "...
user321359's user avatar
1 vote
2 answers
49 views

Running Average calculations

I have a running total for a particular month for our ad spend (marketing). I update this number daily, but I do not enter it for each day, rather I just update it each day in one cell. For instance,...
Nicole's user avatar
  • 11
0 votes
1 answer
80 views

How do I subtract 1 from a cell's value if another cell contains a specific word?

How do I subtract the amount of 1 from cell B2 if the word "text" is written into cell A1?
Avve's user avatar
  • 19
1 vote
1 answer
38 views

How can I format phone numbers from a Google Form in Google Sheets?

I have a Google Form which sends responses to a Google spreadsheet. One of the form inputs is a phone number. In Google Sheets, I have formatted the column containing the phone numbers as "###-###...
Matthew's user avatar
  • 11
2 votes
2 answers
75 views

Google Sheets how to extract nth decimal number using INT() or TRUNC()?

I attempted to extract the first decimal number from a dataset through a very simplistic use of INT(), and was dumbfounded to discover that both INT() and TRUNC() can return 0 out of a calculated 1 : ...
klonaway's user avatar
  • 123
-1 votes
1 answer
30 views

How can I summarise selected items in a tick-box grid of information in Google sheets?

I have a two-dimensional grid of information (link to actual sheet), like so: As you can see, there are items (money, time, and so on) that might be selected for each year. The items are grouped into ...
Daniele Procida's user avatar
0 votes
1 answer
18 views

Convert whole numbers to "text" on Google Sheets [duplicate]

FYI I know close to nothing about how functions work and etc so forgive me if I'm dumb but I've been trying to convert the 33000000 number into the text form on the left automatically. The 33000000 (...
eleaga's user avatar
  • 1
0 votes
0 answers
20 views

Need a formula for Google Sheets to pull information from one sheet into another

I want to create a google excel sheet that will populate student names into cells based on what room they have chosen for rewards day. Students will pick the room they will go to in a google form. ...
user avatar
-2 votes
2 answers
43 views

Can't make my code work with ARRAYFUNCTION

I'm new to this, but I finally got something working that does what I need in Google Sheets, and I got it all into one cell. But it won't work with the ARRAYFUNCTION which is necessary for the sheet I'...
ZweiHydra's user avatar
2 votes
2 answers
56 views

Reference contents of a range against a cell's text in Sheets?

I've tried looking at answers here and elsewhere but can't really find anything relevant or that works. What I'm trying to automate is to look at one cell in a column, then look at a range in a ...
ZweiHydra's user avatar
2 votes
2 answers
56 views

Repeating a regexextract condition

In sheets, say I have this: Word Onset bɐ.ˈɾi.ɕiɾ ˈbɑː.tiɾ ˌbɛs.bɛ.ˈtɾɑɪ̯.ɕe My regexextract in Onset looks like this: =REGEXEXTRACT(A109,"[\.ʔ]ˈ?ˌ?([^ɐɛiouɪɑʊɔ]+)[ɐɛiouɪɑʊɔ]") Aka: ...
skinpuppet's user avatar
1 vote
1 answer
53 views

What strategy or technique would you adopt for a cell lookup if you can't rely on knowing its position?

Suppose I have a sheet like the one below (public link to the sheet), in which I have a list of teams (Red, Blue, etc), and a set of tasks for them each (collect data, agree rules, etc). In the ...
Daniele Procida's user avatar
0 votes
1 answer
37 views

How to create a Column in Google Sheets that outputs the values that at some point were the biggest or smallest from another column?

In this spreadsheet, I have three base columns, "Guess", "Answer" and "Absolute difference". A guess is said to be the best one so far if its absolute difference from the ...
user321015's user avatar
1 vote
1 answer
52 views

Google Sheets Query [duplicate]

With reference to the question how to remove sum label from query, I have a similar situation. Where would I put the LABEL sum(E) in this formula? =if(($B2="weekday"), ifna(QUERY('Base Data'!...
Simon French's user avatar
0 votes
1 answer
31 views

Convert US dates to international format in Google Sheets

I have a Google spreadsheet which I use to track my accounts. I need to add Etsy sales to this, and have added a new sheet which is pasted from the CSV supplied by Etsy. Unfortunately Etsy only ...
dansumption's user avatar
0 votes
1 answer
91 views

Freeze the value of a cell when using a formula and external (volatile) variables

Apologies if the title question is unclear, please feel free to suggest any better wordings of it. I'd like to know if there is any sort of system in place where I can "freeze" the value in ...
Sander's user avatar
  • 3
0 votes
1 answer
109 views

Google Sheet combine rows with same date

I'm loosing my mind with problem in Google Sheets. We are using Appsheet to insert data for daily revenue for different services. So rows are days and columns are services with revenue. I have several ...
Karnak's user avatar
  • 103
1 vote
1 answer
26 views

Copy paste text and values if 2 cells match

I'm a perfumer and I've built a data base within Google Sheets, in which each ingredient's regulation is indicated. So for example: Ingredient Regulation. Coumarin YES 1223/2009 & this for all ...
Hyacinte's user avatar
3 votes
2 answers
94 views

Can multiple forms store their results in the same spreadsheet? [duplicate]

I have multiple Google forms with matching questions. Is it possible to keep the forms separate but see their results in the same Google spreadsheet?
Jon Ferson's user avatar
0 votes
1 answer
20 views

Help with countifs formula [closed]

I need help with a countifs formula. I need to know how many candidate each of my sourceur (column A) has contacted on a specific date (column I)
Harold F's user avatar
4 votes
2 answers
556 views

Is there a way to return multiple columns with XLOOKUP within an ARRAYFORMULA?

I'm using XLOOKUP to return a row with 4 columns. At the moment I have to copy/paste the formula every time new rows are inserted, so I thought of using ARRAYFORMULA to skip this step. The problem is ...
DSantiagoBC's user avatar
1 vote
1 answer
35 views

Google Sheets scatter plot stretches to fit my screen, how do I stop that?

I'm (unfortunately) stuck on my phone without access to a laptop most day, so when I'm messing around in Google Sheets, my scatter plots end up awfully stretched/squashed: As you can see the grid ...
AncientSwordRage's user avatar
3 votes
2 answers
88 views

Use variable in row reference in formula

This is my sheet: (shared publicly) https://docs.google.com/spreadsheets/d/1Pz5dkSHwSXne6f_tMoBJ1Fu3Z6JsvjTQgJZhHhpjia0/edit?usp=sharing In the "Flatten" tab on that sheet, in A1 I have a ...
bw00st3r's user avatar
0 votes
1 answer
43 views

Filter row based on matching substrings in cell values of another row

I have some data from which I'd like to return only cells that contain specific text. In Row 1 I have a sequence of numbers, from 1 to 5, and in the row below each number a text string. I want to ...
Georgi Slaveev's user avatar
1 vote
1 answer
67 views

SUMIF Not working in Google sheets

Found the problem, but not a way to fix it. I'm European and we use , instead of . to show decimals. so 2,50 euros for example is a value. How can i have a formula like =SUMIF(E:E, "A", I:I) ...
A.bakker's user avatar
  • 113
0 votes
1 answer
24 views

Can we hide a Vlookup formula to enter new data in the same cell?

I have created a form in Google Sheets: Row 5, Client, is a dropdown populated by existing clients. Rows 6 to 9, Street Address, City, State, and Post Code, use VLOOKUP formulas to retrieve data ...
Marjorie Reyes's user avatar
0 votes
0 answers
15 views

Convert String to Cell Reference [duplicate]

Hi I hope someone can help, is it possible to convert a String "A1" back to a Cell refrence A1 to use in formulas? Many thanks in advance Mighty Oak
Mighty Oak's user avatar
2 votes
1 answer
43 views

Selecting multiple ranges in a Google Sheets formula using a Mac

I'm having issues using keyboard shortcuts to select a range for any variable after the first variable in a Google Sheets function. Here is a simple example. Let's say I'm trying to write a sumif ...
Julie Ferrara's user avatar
0 votes
2 answers
41 views

Join two columns and add a consecutive number at the end, but only when there's a certain text in one of the columns

I have three columns (cross_type, parent_cross_ID, and transplant_ID). I want to join the cross_type and parent_cross_ID columns, but I want to add a consecutive number at the end (behind either an ...
Irene Martinez's user avatar
0 votes
1 answer
45 views

onEdit trigger causes updated date to be returned as a number

I currently have a Google script bound to my spreadsheet using an onEdit trigger to allow multiple dropdowns. However, it is affecting my columns with dates, changing them from date format "DD/MM/...
Harry W's user avatar
0 votes
1 answer
32 views

Google sheets - Look for a cell linked to a date in a different table and output another cell

I've been strugling with a problem for quite some time. I'm trying to look up the cell C13 in the top table to output the corresponding number in column A of that Table. So for 01/01 monday it would ...
kyra's user avatar
  • 3
1 vote
1 answer
56 views

Downloading CSV results in empty (#ref) file. Is it a problem with IMPORTRANGE? [duplicate]

I have a multi sheet workbook in Google Sheets. I need to export a compilation of tables from this workbook to CSV. I therefore have added a new sheet where I use IMPORTRANGE to combine values from ...
d-b's user avatar
  • 1