Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
2answers
25 views

Simple Google Sheet Macro. Save it, Run it, Fail it… Range Not Found

Not sure what's going on here. I want to copy the data from one tabbed "sheet" within my Google Sheet to another. I set up Record macro. Navigate to data sheet tab. Copy all (via click in top ...
1
vote
1answer
55 views

Google Apps Script: Is it possible to log the use of the close button on a Modal Dialogue?

I'm working on a spreadsheet which opens a modal dialogue when a script begins to execute. Inside it plays a google-style "working" animation made with CSS: If I could remove the close button so ...
0
votes
0answers
19 views

Formula is not working when adding ArrayFormula

I am making a 3d Print Log so I can keep track of my prints, their Material cost. EDIT: Seems part of my post got deleted when I submitted. But your suggestion worked. Thanks so much! =ARRAYFORMULA(...
0
votes
1answer
20 views

Can I retrieve the names of sheets in a public document?

I am currently using the function IMPORTRANGE in a Google spreadsheet to pull information from a public document using the URL. I have to supply a range_string in the format "Sheet Name!A1:D21" to ...
0
votes
2answers
51 views

How to Clear Cell(s) based on the Value of another Cell

I am looking for a script that will clear cell(s) when the value of another cell changes or is equal to ("X"). In the attachment I would like when Column "G" gets checked marked (TRUE), then ...
1
vote
1answer
68 views

How to concatenate several “filled” time slots in a spreadsheet and then check if they completely fill a “main” time slot?

For each row that represents a day of the month (month day 1, month day 2, month day 3 and so on...), I need this spreadsheet to answer NO at column C if no employee will be in the office for at least ...
1
vote
0answers
27 views

How to filter a query by one more column

As a data analyst, I'm trying to get a specific string from Google Sheets with a query, so that my table can be more filtered with useful information. This is my current query which is working, but ...
1
vote
1answer
28 views

Formula Error: =IF( SEARCH() ); Visual Reference to Question

I know I'm very close with my formula: =IF(SEARCH("Wednesday", (A3:A)), "workday", 0), But I must be missing something here...: I can't get this formula to display the value "-" when I run it on a ...
2
votes
1answer
36 views

How to find, out of many days, 1 day that is most profitable on Google Sheets?

Ok, I've been looking for this type of formula but not finding it. So here I am asking for help. I'm a day trader on stocks and I want to analyze my performance. For example: +-----------------+-----...
1
vote
0answers
10 views

How do I auto timestamp a sheet when something is moved to it

I am using this function to automatically move a row to a secondary sheet when a set value is entered. function onEdit() { var sheetNameToWatch = "Tracker"; var columnNumberToWatch = 4; var ...
0
votes
1answer
27 views

Change a date reference as text to Google Sheets date format

Need to change "December 23, 2019 at 08:31PM" to a date Google Sheets recognize.
-1
votes
1answer
16 views

No logger output in sheets

I am getting no output from the logger in sheets. I am a boob, although a 30+ year programmer. Here is my function: function onChange(e) { var spreadsheet = e.source; var cell = spreadsheet....
1
vote
0answers
16 views

Google Sheets: insert a non http protocol link?

I use the Chrome extension Session Buddy to store browsing sessions. In this case, I saved a few windows with tabs on a search I just did but will read later. So, on a Sheet where I keep notes to ...
1
vote
1answer
28 views

Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable

Overview I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is ...
1
vote
1answer
34 views

Problem with COUNTIF in Google Sheets Data Validation Custom Formula

I am following some tutorials and discussion on forcing uniqueness in Google Sheet Column (I need it for ID indexing). I have been using Data Validation for some time, but i have a problem with custom ...
1
vote
1answer
15 views

What does .activate do in google scripts?

sheetFunction { var sourceFile = SpreadsheetApp.openById("1asdasdasdasdasd"); // Set sheet var masterSheet = sourceFile.getSheetByName("Master"); masterSheet.activate } cellFunction { ...
0
votes
1answer
21 views

Limit number of rows

I have a Google Sheets document that keeps adding hundreds of rows and slowing down the project, and sometimes causing it to crash. Is there a way to limit the number of rows to say 100. I do not ...
1
vote
1answer
37 views

Excel to Google Sheets Script

I am using a script below for excel to pull a certain row value from one sheet to another cell on a different sheet. I just want to know if this can be done with google sheets thru script and if it's ...
1
vote
1answer
26 views

If “text” then, how to put a conditional formula

There are three columns. A, B and results. I need to make a formula that gives the following: If the text in both columns A and B is WIN, then in the results we should see: Winner. If the text in ...
1
vote
2answers
90 views

“Error Did not find value '____' in VLOOKUP evaluation.”

Please see my public spreadsheet here: https://docs.google.com/spreadsheets/d/11RMN7uN_woEQVzlDEkX6vSARTCGYYBjWs11ZDU-6Shc/edit#gid=1276951687 My goal is to get the Location Code from tab 1 into tab ...
0
votes
1answer
15 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 ...
2
votes
1answer
48 views

COUNTIF with Multiplicities

I am currently creating a histogram on Google Sheets to count how many of x-product in our inventory we have priced at less than $10k, between $10k to $20k, $20k to $30k, etc. The problem is that ...
2
votes
0answers
26 views

Auto sorting by multiple columns in Google Sheets

I have a data-set which I would like to have sorted with first column 6 and then column 7. From this post: How can I make some data on a Google Sheets auto-sorting? I have gotten ideas about making a ...
0
votes
1answer
28 views

Define max and minimum series for Google Sheets

I have a line chart as the picture below. The max and min values in my data are 410 and 290 for the peakflow series (the chart's visible range is something like 0 and 500 which is a bit pointless as ...
0
votes
0answers
39 views

Avoid Duplicate Events in Auto Sync Google Sheets to Google Calendar

I wish to use this sync process but it will duplicate the events if I do any changes or editing. What should I do to avoid it? function scheduleShifts() { /** Task 1) Open the Event Calendar. ...
1
vote
1answer
21 views

Search for missing data starting with certain text

Hello I need to compare two columns and output all missing data into a third column. The problem is that I need to also filter the first column for only certain cells that start with two letters ...
0
votes
1answer
18 views

Updating slides from sheet

I am trying to update slides from sheet. As simple as that. I have a working script in sheet which updates the slide if I run it from the gscript editor I want this script to run onEdit of specific ...
-1
votes
1answer
21 views

What Add ons are available to give Google Sheets features similar to Excel Tables and Structured References?

This article: https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-F5ED2452-2337-4F71-BED3-C8AE6D2B276E describes the use of structured references. I hadn't heard ...
1
vote
1answer
35 views

Dynamic Query using TRUE or FALSE in Google Sheets

I have a checkbox in Column R. (values are TRUE OR FASLE). I want a dynamic query. I will ask the user to select a value in B2 where "true" or "false" is selected. The below query is working (no ...
1
vote
0answers
13 views

Date Formatting Issue in Google Sheets Query

I created a shared shipment tracker with google sheets where updates are made in a master tab, then filtered into several regional tabs via query function to better visualize shipment status by region....
0
votes
1answer
10 views

Point in time data

In google sheets, was wondering if I can do a lookup and capture the data of the return at that point in time instead of continually updating it. For example, if I'm using vlookup to relate item to ...
0
votes
0answers
10 views

Dynamic calculation of what one cell value would be if another cell (used as input for range) had a different Value

I have a spread sheet with an itinerary containing itemized day to day travel. On the sheet there is a large range accounting for variable costs, in which a specific cell (We'll call it K10) says how ...
0
votes
1answer
28 views

How to open multiple urls in different new browser tabs from Google Sheets with a single click?

I have a row with multiple urls on it. I'd like to open each url in a separate browser tab without having to click on each link. Is this possible in Google Sheets or Google Apps Script?
0
votes
1answer
17 views

Assigning ID automatically using Arrayformula and text

I'm probably approaching this wrong but I'm trying to automatically assign an ID to an item, I'd like the format to be C#### where #### starts with 0s and is assigned as 0001. The expected output ...
0
votes
1answer
56 views

How can i get data from Google Sheets to Google Forms dropdown list?

I have some data over Google Sheets file, i want to get Google Forms dropdown list to be updated automatically from that list. Is it possible? Thanks
1
vote
1answer
13 views

How to apply “sendAlertEmailWhenCellValueIsNotEmpty” script to multiple tabs in Google Sheets

I would like to apply this to let's say "Weekly" and "Monthly" tab. Here's my script: function sendAlertEmailWhenCellValueIsNotEmpty(){ //send an email alert when cellToWatch is not empty //set ...
2
votes
1answer
25 views

Filter function with multiple variables google sheets

I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T ...
0
votes
0answers
6 views

Why are these fractions evaluating to incorrect values in Google Sheets? [duplicate]

I'm working on problem which involves generating the field of the positive rationals in Google Sheets. Here is a link. For some reason, the results of many of the fractions are incorrect. For ...
0
votes
1answer
17 views

Add single value as constant line to diagram

I have a range A1:B of which I make a simple line diagram. I also have a single value in another cell, say C1, which I want to use as a constant reference line in the diagram. I know I could populate ...
0
votes
1answer
22 views

Google Sheets split by “ ,” & add new rows

This is an example: I need to split by " , " in a new row and duplicate the others The desired output on a new sheet:
0
votes
1answer
18 views

Google Sheets: Fill out dates and replace blank cells with zeroes

I'm trying to make a timeline graph of seed sales, and right now it's almost completely unuseable: I think there are two reasons for this: Not all dates have values associated with them, and ...
0
votes
1answer
20 views

Adding Positive Values Only (across multiple sheets)

The example formula below works perfectly in my spreadsheet if ALL the numbers are positive. However, I would like to pull the values from the cells indicated below (from 14 different pages), but ...
0
votes
1answer
41 views

Need to create a formula with the operand in a different cell in Google Sheets

For eg . Existing formula which works Query(A4:K,"Select A Where I = > 0.3") should be replaced by Query(A4:K,"Select A Where I = "&N1&"") with cell N1 containing value '>0.3' Appreciate ...
0
votes
1answer
10 views

Prevent anonymous users from creating new rows?

Hi everyone Im wondering if you can help me. I have a google sheet that I need to allow anonymous users to access. I have locked down all cells bar 3 that I want people to be able to edit. ...
0
votes
1answer
42 views

Write both values and formulas at once in Google Apps Script

The row in my spreadsheet has format <value>, <formula>, <value>, <formula>, ... I need to update this row. Indeed, I can't use range.setFormulas, because it has values and ...
0
votes
0answers
15 views

Turn off viewer icons (and names)

I'd like to share a Google file (document/sheet/slide) but have the little viewer color bubbles in the top-right corner disabled such that viewers can't see how many others are viewing concurrently. ...
0
votes
1answer
28 views

Increment value based on condition

I'm trying to combine arrayformula with an increment value based on a condition. Basically I have a column called purpose, it basically has 2 values - Restock and Order. Upon order, column Count ...
-1
votes
1answer
25 views

How to add Print buttom in a sheet to print other sheet

I need a script to, adding a print button in a sheet1, to print the entire sheet2. How can I do this? Many thanks
2
votes
1answer
26 views

Is there a way to copy text from two cells into another spreadsheet only if another cell has a YES?

I want to copy the text in Cells A1 and B1 into another spreadsheet that will be emailed out, but only if the text in Cell E1 has a "YES" in it. How would I go about doing something like this in ...
0
votes
1answer
24 views

How to fill columns automatically when a new row is added

Been trying this for a few hours, trying to fill down column F G H automatically when a new row is added (my data is on another sheet that's coming in from forms) https://docs.google.com/spreadsheets/...