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

Why is my Google Sheets importrange repeatedly throwing internal errors?

Today several Import Range functions in one file started throwing internal errors. I got things working and within a couple hours the same thing happened again. Have I hit some limit, or some ...
0
votes
1answer
9 views

How can I create a line chart in Gsheet if I have a pivot table with 2 rows?

I want to plot a line graph, it should have 4 series as mentioned below Car 2019 Scooter 2019 Car 2020 Scooter 2020
0
votes
1answer
19 views

How to choose encoding when export a CSV on google spreadsheet without using Google Apps Script or Google APIs?

My goal is to export a CSV from google sheets using encoding UTF-16. Google sheets is by default exporting CSV in UTF-8 and uses commas to separate values. I would like to export the CSV in UTF-16 (...
0
votes
2answers
24 views

Google spreadsheets have a column limit?

I read that google spreadsheets have a column limit of something like 20000. However my spreadsheet does not have any column beyond AC. How can I add columns to my spreadsheet?? (in Excel this is ...
-1
votes
1answer
15 views

Autocalculate Date to match a number in a given cell

I'm trying to create a formula to calculate the date in column D4 based on the 'month & year' provided in B4 and a plain-text number given in C4 so if B4 is 02/2021 and C4 is ' 1 ' then ...
1
vote
1answer
36 views

Arrayformula to get closest non-numeric value above

I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example: Title| 1|Title 2|Title Next ...
0
votes
1answer
18 views

Google sheet: run a script via the share link (without login)

There is a script for my Google spreadsheet so that every change in a cell is indicated by a red color marker. When users with a Google account that I have shared to the spreadsheet make changes, the ...
-1
votes
1answer
31 views

QueryByName() function from another sheet in google spreadsheet

Is it possible to use the QueryByName() function using another sheet from the same spreadsheet? =query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0")) I ...
0
votes
1answer
53 views

How to use a query with a combination of If and Where

I have a data base on Google sheet. See the spreadsheet at this link:https://docs.google.com/spreadsheets/d/1NGkH56Ga95osN8eBI6d5MhV7SnoEPK4Oe7ymRJVK7M0/edit#gid=1262146092 It s a copy so feel free to ...
0
votes
1answer
32 views

Google sheets conditional formatting to show selected text

So i've been trying to find a way to do this but struck a wall so hoping someone here might have a idea! Problem: We have a list of signatures that can be choosen with dropdown list in Google sheets. ...
0
votes
1answer
21 views

Query formula to import the data from multiple tab sheets

I am using a query to import the data from multiple sheets, however, the formula is skipping empty cells. Is it possible to not skip empty cells and keep the data in the correct cell on the Master? ...
0
votes
1answer
30 views

How to format a google sheet that's capturing google form data?

I have a google form which I linked to a google sheet via the "create spreadsheet" button on the form. Is there a way to format the resulting google sheet for current and future form ...
0
votes
2answers
37 views

Compare Two Dates and Display The Earlier One

Cells B2 and B3 each have a date entered in them. I want to compare the two dates and display the earlier date in cell C2. Or, stated another way: if B2 < B3, display B2 in cell C2 if B3 < B2, ...
0
votes
1answer
21 views

Is it possible to write a script that automatically triggers some function within a Google Sheets add-on?

I'm using some Google Sheets add-on which pulls information from API databases, and it has both a free subscription and paid subscriptions which add many additional abilities which I don't really need,...
1
vote
1answer
45 views

How to get weekly averages for workout tracker data in Google Sheets?

I am making a personal training app that uses data from Google Sheets. I want to create a formula that takes the data from a sheet of workout tracking inputs and creates a weekly average for each ...
0
votes
1answer
28 views

How to view all possible values between two columns of data

I have columns A, and B, and I want to divide every value in A by the values in B. For example, divide 15 by all values in column B, once done, shift down and divide 57 by all values in column B etc. ...
0
votes
1answer
25 views

Getting "Exceeded maximum execution time (line 0)" error with custom code when data input increased in "History" sheet

I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively. I have a custom function that takes data in the "History" sheet and arrange ...
0
votes
1answer
25 views

If any contains do X if not do Y

I am trying to work out the proper formula for this. If any of these cells (A2, B4, C6, etc) contain a value > 0, return -1. If not then do this IF statement. Essentially an IF ELSE statement, but ...
0
votes
1answer
26 views

Go to last data row in google sheets

I have a google sheet that has many pages inside. How can I go to the last data row quickly when I change the page? I tried ctrl+end but this goes to line 1007. ctrl+↓ not working either.
0
votes
2answers
21 views

Assign a default name if the cell value is null

I have a booking form, shown. I also have a template form, hidden. This script is assigned to a button to create completed bookings by copying the booking form data to a copy of the template and again ...
0
votes
1answer
17 views

Combining year date into autofill google sheet

So I am trying to create a basic daily finances tracker using google sheets. And I want to be able to have it so that yearly tabs reference a template sheet that allows you to change the year on each ...
0
votes
1answer
14 views

How to enable macros

Looking for some insight. In Google Sheets I added a series of buttons linked to macros. Works just fine for me and another user. Other users are telling me that they click the button and nothing ...
0
votes
2answers
18 views

Copy random row from one Google Sheet to another

I have 2 separate google sheets. (Lets call them Primary and Secondary) The Primary spreadsheet has 2 columns with 50 rows of data. On a set interval (weekly) I want to copy only one random row from ...
0
votes
1answer
31 views

Update a sheet row by matching a value from another sheet

I'm new to App Script and I'm trying to update an existing row by matching value coming from another sheet. Basically record all the data from the "receipt" to the "record" by ...
1
vote
1answer
36 views

How to copy a cell from Google Sheets without getting double quotes (" becoming ""), BUT keeping the \n (char(10)) line breaks

I have a couple of Google Spreadsheets I use to generate config files for things. The config file data ends up in a single cell, which is copied and pasted. Problem is Google Sheets has this odd ...
0
votes
1answer
17 views

Offset a Vlookup across multiple sheets

I recently received help on creating a formula to fill in data on a Google sheet for customer orders, this goes between sheet1 (master) and sheet2 (selected data only, depending on the reference cell)....
1
vote
1answer
52 views

How to stop google sheets resizing an image with =image("url")?

This sheet shows the problem: https://docs.google.com/spreadsheets/d/1G_SXx_poARQ4muv3wxopV-zsSOoRwdNgssrvnfFOCVg/edit?usp=sharing I have an image that is 41px by 1637px (https://i.imgur.com/dVhtrwT....
0
votes
1answer
27 views

How to sum one row of multiple selected columns where a col matches a condition, but return null string instead of "0" if no match

Say you have one spreadsheet like this: A B C D E F Timestamp Name P1 P2 P3 P4 10/17/2021 16:28:47 Asha 1.2 3 .5 3.1 10/17/2021 23:01:38 Wei 0 0 0 0 Now, in another spreadsheet, you have ...
0
votes
3answers
45 views

Extract URL from hyperlink in Google Sheets where there is no `hyperlink()` function being used

I've got a hyperlink that I've copied from a website and put into a Google Sheet. If you hover over the hyperlink it shows you it is an active link and you can click it. But if you view the formula in ...
0
votes
1answer
31 views

Google sheets reference Text in Cell in Array Formula & Countif formula

So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
0
votes
0answers
9 views

How to add pop up calendar depending on another cell input?

I would like to create a column of cells (assuming in Column A) for user to select the status of job, eg. In progress, Closed, KIV, while Column B is for the Date the job is closed. Column A is rather ...
0
votes
1answer
14 views

Google forms to google sheets to google doc - How to set date to display dd-mmm-yy in sheets and doc?

I'm fairly new to coding, and am trying to get my head around a problem for some work admin. I´m hoping there's a quick and simple fix to this. So as the title says, we enter info on a google form and ...
1
vote
1answer
18 views

Using OFFSET function across multiple sheets

New to Google sheets, so feel like I'm falling down somewhat of a rabbit hole trying to figure this out... I have a spreadsheet 'sheet 1' where we keep the details of our sales - Name, address, design,...
0
votes
2answers
51 views

Add constant number to query() result in Google Sheets

I've a QUERY() function that results into this automatically generated table: A B S 2 F 5 G 1 I'd like to add a constant value to each cell in column B like (let's say 3): A B S 5 F 8 G 4
0
votes
0answers
15 views

Conditional Format cell if the value is just text or a reference [duplicate]

How to Conditional Format cell in Spreadsheet based on whether the value is just text or a referenced cell from another tab
0
votes
0answers
11 views

Creating a Button that allows you to Print a select area of cells

I have a document here that I needed some help on! https://docs.google.com/spreadsheets/d/1AFcHk-5JxMxj1ma-vTmCHIwBVn81Mgmxl001k__IMT4/edit#gid=0 I'd like to create multiple buttons in one tab that, ...
0
votes
0answers
49 views

Array Formula Problem - How to Fill In Blank Rows with the Same Value before a Breakpoint which advances to the next letter

I have a Google Sheet which is grouped into segments of a TV show. So there's an A segment and then a B segment and so on ... I have allocated two columns to provide two different timing functions (...
1
vote
3answers
29 views

Removing shared files on Google Drive

I am trying to clear out google files that I do not need. They are documents my children needed for school during the COVID shutdown. I was able to remove several of them however there are some that I ...
0
votes
0answers
6 views

Google Sheets row height not the same between current sheet vs selected cells on printing

When printing, the row heights are preserved on picking 'Current Sheet' But is shrunk when picking 'Selected Cells' Is there a way for this to look the same?
0
votes
2answers
25 views

How do I add an Apps Script function to a Google Sheets cell?

I'm trying to figure out how to count the amount of times a group has clicked on a specific cell containing a cell. I've gotten it to work with having a separate drawing next to it with the function ...
0
votes
1answer
13 views

Google Sheets Script - Duplicate inactive sheet and Rename based in active cell data

I have a working sheet, "Booking Form". I have a template of the working sheet, "Exported". What I would like to do in this function is; duplicate template sheet "Exported&...
0
votes
1answer
16 views

End of day data with trigger in google sheets

I want to copy/paste the values of a specific column into the next column using a trigger at a certain time of the day. The script works when I run it manually, it runs as per 'last run' via trigger ...
0
votes
1answer
50 views

Google Sheets Drop Down Menus that remove used options

I am attempting to use Google Sheets as a scheduler of sorts for some rooms. A1:A4 is a list of room names. B1:B4 is using this formula =IF(COUNTIF($H$4:$H$6,A1)>=1,"",ROW()) Where the H ...
0
votes
1answer
10 views

Google Sheets. I'm trying to insert a row of CheckBoxes IF B2 Cell has text [closed]

The trick part is... i can't figure out a way to do it, the formulas don't let you add checkboxes. I tried using the code from Google Script to insert a Checkbox but nonetheless i get nowhere. Please, ...
0
votes
1answer
22 views

How to keep my chart unchanged when deleting rows from data source?

Could anyone kindly advise how to keep my sample chart unchanged after deleting the unnecessary Row 2? I have a Google Sheet that contains over 50 charts whose data source is within only one big data ...
0
votes
1answer
18 views

Mail merge BUT body is like address label merge (multiple spreadsheet rows per output) while header and end act like regular mail merge (1 row per...)

The header and end of the template need to operate like mail merge, one set of data per document, while the central body of the template needs to operate like address-label-mail-merge, one row per ...
0
votes
1answer
19 views

Google Sheets- How to format a cell to change color on a future date

I'm trying to format a cell in google sheets to change colors when it reaches a certain date. For example, if I call a customer on 10/10/2021, I want to format a cell in the row to turn red on 10/20/...
1
vote
1answer
27 views

How do I filter cells by a list of keywords, and import them into another sheet in a workbook?

I've got a list of customer feedback from a company's website. We'd like to filter the data by store, so we can shout out the staff members or stores that are doing particularly well, and give them ...
0
votes
0answers
10 views

Google Sheets - Repeat row in coordination with other row [duplicate]

So I've been looking to do this for a long time hours now, maybe I haven't used the right search terms, but I can't find any working formulas on any discussions! Here's what I want to do: So here's my ...
0
votes
1answer
13 views

Using a cell to fill out/finish a formula

So I am currently making a calculator, but need to make it as simple as possible. Currently I have it like this: =IFERROR(query('Calculator Data'!B:H,"Select F where C='105 Kira Tusami'", &...