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
1
vote
2answers
73 views

Can I use Google Sheets to Automatically Update Font Styling, Size, Color, etc.?

I have been creating a contact list in Google Sheets to continually add and update names, emails, organizations, etc. for work. I am often copying and pasting this information from other sources as to ...
0
votes
1answer
26 views

Can a publicly shared file stored in my Google Drive be deleted by someone having “Anyone with the link” editor rights?

Following a concern expressed by a user at How to share a Google Sheets demo spreadsheet. The problem with sharing it editable is that anyone can trash the sheet. Surely it's better just to share it ...
0
votes
1answer
16 views

Cannot figure out how to change selected cell

I have the following script: function onSelectionChange(e) { const sheet = SpreadsheetApp.getActiveSheet(); const resultA1Notation = "D2"; // Change const range = e.range; const ...
0
votes
1answer
915 views

How can I disable the authorization needed to open Google Sheets Macros?

Whenever I try to open any macro enabled sheets it requires authorization, even though it's a first party application. Is there any way to stop Sheets from asking it or give permissions to everyone I ...
0
votes
1answer
22 views

Google Sheets: Supplement cells automatically

My goal is a function/macro/script that automatically inserts a text in every selected cell by a key combination (or similar). The inserted text needs to stand at the beginning as seen in the example ...
-1
votes
1answer
37 views

Goggle sheets linking different sheets

Using Google Drive can I link a whole worksheet from a shared with me file to insert it as a sheet into a workbook that I created in my drive. I don't want to link cell range values, I want the whole ...
11
votes
5answers
30k views

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

I have a filter view in Google Sheets that does not refresh when new data comes in via Google Forms. To refresh the filter view, I can remove the filter and apply it again, or even just click "OK" in ...
0
votes
1answer
50 views

Using UNIQUE and SORT together

I'm currently using UNIQUE to look through a spreadsheet and display in another spreadsheet all the unique names in a certain "Name" column. However, I need to sort these names by another column ...
0
votes
1answer
37 views

Is there any way to set different formulas for multiple ranges all at once?

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
1
vote
1answer
233 views

Incorrect range height, was 64 but should be 938 using setValues

I am attempting to loop through different latitudes and longitudes of a location and a destination and an arrival time through Google Directions APIs specified in my spreadsheet. function ...
0
votes
1answer
32 views

Send a Google Forms notification to two differenz email addresses based on

How can I send an automatic email notification to two different email addresses depending on whether a woman or a man has filled in the form? Please can someone help me with a script to check this ...
0
votes
2answers
25 views

Query twice in the same cell to receive a name next to a number in a row

Sheet link I posted before however, I don't think I was specific enough. I am sorry if I am wasting the time of anyone. In my sheet, you will see three pages. On the "Search Notes" page in C5 I ...
3
votes
1answer
2k views

Reference Cells in Sorted Filter View

I have a Google Sheet that looks like this: That Google Sheet has some Google Script that looks like Google This: function myFunction() { var ss = SpreadsheetApp.getActiveSheet(); var rng = ss....
0
votes
1answer
20 views

Comparing data changes over time?

Let’s say I’ve collected data on a scale of bins from 1-6 at two different time periods (T1 & T2), organized as two columns in Google Sheets: T1 T2 1 2 1 5 2 3 5 ...
2
votes
1answer
1k views

Change function from running only on Active Sheet to run on All Sheets (tabs)

I have a script (from here) that changes tab name based on cell value. This works great on the active sheet but I would like it to check and change all tabs based on cell B2 of each tab. function ...
0
votes
1answer
35 views

How to make arrival time in Google Sheets

Hello I would like to ask how to calculate the arrival time. For example: I have 1 cell where I put km which are left for 1 truck. I want to calculate the current time + the cell where I write the km ...
0
votes
1answer
47 views

Increment the numeric value of Col D by 1 if Col E is “True”

I have tried to follow the example below to color E to Red if E = True Figured I would keep changing the script until What I want is to increment the value in Col D by 1 if the Value of Col E is "...
0
votes
1answer
34 views

Query with sums and 2 columns on the left

I have problem with the query: =Query(Raw_Data!J$2:$BL,"Select J, Sum(U),Sum(V),Sum(W),Sum(X),Sum(Y),Sum(Z),Sum(AA),Sum(AB),Sum(AC),Sum(AD),Sum(AE),Sum(AF) where J contains 'CUS' and (AL='1-Revenues'...
0
votes
1answer
34 views

Sheets date formula help

Im trying to find a way to input a due date associated with a task with a column next to it with check marks that can clicked once the task is complete, if it doesnt get checked by the due date I'd ...
0
votes
1answer
378 views

How can I get GoogleFinance to see the return of a stock from a specified date range?

How can I see the percentage of return from a stock from a specified date range to today? For example: February 19, 2020 to today = -31.93%. So far I have: =GOOGLEFINANCE("GOOG","price") / index(...
0
votes
1answer
26 views

Add checkboxes in a column automatically when another column contains a value (or not) in Google Sheets [closed]

I need to automatically add checkboxes in column F but only when column G contains a value in the same row. Conversely, I want a checkbox to disappear or be disabled when the cell in column G in the ...
0
votes
1answer
10 views

Send email when cell value equals 29 [duplicate]

I am having trouble with my google sheets script and I wondered if anyone had any advice for a beginner. I want my script to check a cell value (Sheet1, H2), then send an email if the value = 29. ...
1
vote
1answer
751 views

Google script to delete on condition

I'm trying to fix a script for a Google Spreadsheet, https://docs.google.com/spreadsheets/d/1J3VRBXkx5B3p1fmrVpkI3Y6F5EGns9Vao7FyxOQsSVo/edit?usp=sharing I wrote to following: function ...
0
votes
2answers
18 views

Function/formula to return only the first string proceeding the first carriage return

Is there a function/formula to return only the first string proceeding the first carriage return using Google Sheets? Example Text: Bob & Mary Smith bob@email.com 555-111-1234 Assuming ...
0
votes
1answer
30 views

Plotting gigantic values + smaller values on the same Google Sheets chart?

I'm trying to plot a chart that includes one data set in billions, another in millions, and a third in thousands. What is the most efficient/elegant way to do this using google sheets without the ...
0
votes
1answer
10 views

How can I use a filter to show everything except cells containing specific words?

I'm importing a Google Calendar (ics link in A2 that I'm importing with an IMPORTDATA function) and it has system dates related to 1970 that I obviously want to filter. How can I specify to show me ...
0
votes
1answer
10 views

Data validation with specific condition

I have special conditions for column in Google Sheets which are: should only be filled with numbers no special characters allowed no alphabets allowed no space allowed should be 16 digits in total I ...
0
votes
1answer
58 views

Sending email form Google Sheet Issue

I took this script from the Google developer site and modified it to suit my needs. The issue i am having is where the "EMAIL-SENT" lands on my sheet. I need it to be placed on the 4th column right ...
-1
votes
0answers
10 views

“Publish to the web” not working with ImportJSON when original file is closed [closed]

I'm unable to reliably share a sheet from Google Sheets with "Publish to the web" reliably, since it displays zeros as values instead of the values I have in the original sheet. I have a ...
0
votes
1answer
309 views

Google sheet formula for previous 7 day high

Need a formula for previous 7 working day high for a share =INDEX(GOOGLEFINANCE("nse:"&A2, "high", TODAY()-3.3), 2, 2) Used the above formula but I need only workday formula.
0
votes
1answer
15 views

How to use multiple criteria with Regexreplace in Google sheet?

I am trying to correct the number/expression of room numbers that are entered by users with the following: 1- If the number contains ( / or \ or _ ), it will be replaced with "-". 2- If the ...
0
votes
0answers
14 views

How to change IFS function to switch? [closed]

I have this formula =ifs(OR(A1="January",$A$1="March",$A$1="May"),$A10,OR($A$1="April",$A$1="June"),$B11,OR($A$1="February"),$C10) I would ...
0
votes
0answers
12 views

how to remove contact from Google contacts when email removed from google sheet using apps script

I have an Apps script when run: reads Google sheet, adds new contact/s ( name and email) into Google Contacts, a duplicate is prevented to be entered into Google contacts as well. However, I want when ...
0
votes
0answers
13 views

How to use Divide function actively? Ex: If I change the value of divider, how do I keep this up to date?

How do I make the values of divided, sum etc. active? I can't figure this out. If I use the =DIVIDE(etc). the values in these cells stay the same if I change the values of divided or divider.
0
votes
2answers
310 views

copy a google form retaining responses and summary charts

I have wrongly submitted a google form to two different groups of people. So I have created a copy of the form after generating the spreadsheet. However, in the responses tab of the copied form, I get ...
0
votes
1answer
610 views

Add row instead of overwriting cells with IMPORTRANGE?

I'm using Google Sheets to automate my flow of creating Quotations and Invoices for my clients. What I am trying to achieve is: I key in the values Name, Dimensions, Description, Amount, and Price ...
0
votes
0answers
15 views

Google Sheet: calculation/math inaccuracy [duplicate]

I found weird math error. I was doing calculation that comes down to this: =TRUNC(253-0.81*300) This comes back as 9, but should be 10. For some reason, when you increase the visible decimal places, ...
0
votes
2answers
90 views

Why does only one of these formulas work in Google Sheets?

B10 = 'High" =SORT(CONCATENATE(B10,"!B3:", B10, "!AK42"), High!AL3:High!AL42, TRUE) That returns the error SORT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: ...
0
votes
1answer
9 views

typing 1 to 9 on keyboard does not trigger editing cell in Google spreadsheet

When on a iPad in Google Sheets and typing 1,2,3,4,5,6,7,8 or 9 does not trigger the editing of a cell. 0 and all other characters do. Is this a new feature/bug in google sheets or can I change a ...
4
votes
3answers
15k views

How can I get GoogleFinance to see 1 year and 5 year return of a stock?

You can use GOOGLEFINANCE("name", "return52") for mutual funds, but not for stocks. Is there another way to get the same result?
0
votes
1answer
30 views

Multiple Groups Graph on Google Sheets. Please Help

I got the following table on google sheets. Text version +----+---+---+----+ | | A | B | C | +----+---+---+----+ | 1 | 1 | 2 | 1 | +----+---+---+----+ | 2 | 2 | 1 | 1 | +----+---+---+----+ | ...
0
votes
1answer
255 views

Google Forms Quiz Response Sheet

I have added a couple of columns to the Google Sheet Responses for a quiz created using Google Forms. When creating the response sheet (created in the "Responses" tab of the quiz, it automatically ...
1
vote
1answer
2k views

Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
0
votes
1answer
588 views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
0
votes
2answers
626 views

Google Sheets checkboxes not rendered correctly on mobile

I am using columns of checkboxes on some Google sheets that I have shared for read-only access. On a mobile device, the users see TRUE or FALSE in the columns instead of the boxes. On a PC it looks ...
0
votes
0answers
22 views

How do you color a cell if no other cell references it? [duplicate]

I am making a spreadsheet with a bunch of variables I want to change and some formulas that use those variables. I want to use conditional formatting (probably?) to color any cells which are "...
0
votes
2answers
2k views

Increment the reference to a cell formula by 1 in Google Sheets

So, I'm wanting to use the following line of code: =image(CONCATENATE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&A2&+B2&+C2&+D2&+E2&+F2&+G2)) ...
0
votes
2answers
4k views

How to rename a series in Google Sheets chart?

By default, a Google Sheets chart will use the first row as the the name of the Series. But if you are selecting a range in the middle of the table (for example, rows 38-100), then you can't use the ...
-1
votes
1answer
11 views

Problems when sorting a Google sheet that someone else is editing

In the past, when two people were editing the same Google sheet, if person A attempted to do a sort they would get a "heads-up" warning that A's sort was going to affect person B. We now don'...
0
votes
0answers
8 views

How to show all formulas (Sum, Avg, count) in Google Sheets when you select multiple cells [closed]

Currently, when you select multiple cells, only 1 function is shown on the bottom right corner, either SUM or MIN or MAX or COUNT. How can I make it so that I quickly see all functions at once?