One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

2
votes
0answers
6 views

Formatting Google Sheet Dates Inconsistent

I have a sheet where various data is entered through a form. Dates are picked on the form using the standard date picker provided by Google Forms. Some dates in a column I can change the date format ...
2
votes
2answers
30 views

Multiple auto-populated select boxes Google Sheets Script UI

I found an example of how to auto-populate a drop-down select box from a Google Sheets range, for a Google Apps Script UI that enters new rows in the spreadsheet. I have so far been unable to figure ...
0
votes
0answers
17 views

Reference a Google Spreadsheet Row containing specific Text within Google App Script for spreadsheets

I am having trouble finding a function that can reference a row (the number of that row) containing specific text as criteria. I thought that VLookUp would work, but it's not really. Ideally it's ...
1
vote
1answer
20 views

Google Sheet conditional formatting, highlight just 1 cell in a range based on another cell

How can I highlight a cell in a range, if its value is equal to a different cell? Example: Random numbers in range A5:A20. I want to highlight a cell in this range if it is equal to B4. [ A ...
2
votes
1answer
16 views

An answer in column B on Google Sheets generated from a Google form sends email to collaborators including all data in that row

I'm a school teacher using Google Forms and would like to generate an email to myself, and if possible other collaborators, based on an text answer of "YES" in column B from the sheet generated by ...
3
votes
1answer
32 views

How to count rows in Google Sheets that do not contain a specific text

I need a formula that will tell me how many projects are going on at any given time in my Google spreadsheet. My table header looks like this: Project | Jan 1-5 | Jan 6-10 | Jan 11-15... And each ...
3
votes
1answer
16 views

why is one row skipped when including ranges from two sheets

I have a spreadsheet with some test data: https://docs.google.com/spreadsheets/d/1Rd8uJp2KTl2ds-s_SWvUSkxNjEscAEiwAO71zSJ2A8Q/edit?usp=sharing. In the 'All Transactions' sheet, why is row 3 from the ...
0
votes
0answers
15 views

Creating a script to add a menu that allows me to add a new column to a specific range

I want to create a script that adds a row to a specific range copying the format and functions and data validations of the row above it. Example Spreadsheet Use the link to the sheet above as a ...
2
votes
1answer
15 views

How to display dynamic range of data from another sheet, but while transforming one column

I have a sheet of data in the form: Date Val 2015-08-05 10 2015-08-09 13 2015-08-14 4 I am trying to display this data in other cell such that it looks like this: Date ...
1
vote
0answers
20 views

Google Sheets selecting filtered rows

When I create a filter in Google Sheets and then do a block selection on the results, copying this copies the rows between them as well. How can I only copy the content that I can actually see using ...
1
vote
1answer
12 views

Using value in cell to reference sheet in conjunction w/ 'arrayformula' function

In cell A4 I have name of sheet (e.g. Sheet1) w/ data values in cells B10:K10 that I would like to copy into cells C4:L4 of current sheet. I'd like to use arrayformula function, however, I am having ...
0
votes
0answers
25 views

Send email function when one cell does not equal another

I have the following code which works if I use == in the comparison of Sdate to Ssheetdate, but I want the email to send if Sdate does not equal Ssheetdate. I've tried > I've tried < and I've ...
1
vote
1answer
7 views

Google Script Replace Function Reference

Is it possible to build a Google Script where I can select a cell, take its function, replace the cell references (instead of C2,C3,C4, etc. make it AC1,AC2,AC3, etc.), and then return the output of ...
0
votes
0answers
9 views

Google Sheets How to change Recalculation “On Change & Every Minute” to Every Second?

I need create count down clock refresh second by second . function "=now()" only can change every minute but I need refresh second by second please help me
3
votes
0answers
12 views

Can I jump to next cell containing invalid data in Google Sheets?

I have a Google sheet containing lots of data, and some of it doesn't respect the data validation rules (and the little red triangle appears). Since I have a lot of data, it's quite a long process to ...
0
votes
0answers
13 views

How to delete a single answer in Google poll/survey

I started a survey with Google form. Everything is fine. But one Answer was accidentally given twice. As I understood, there are two locations of the answers. Internal, of the form and a copy of each ...
2
votes
0answers
42 views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) update

Thanks for a very useful script. Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) I am trying to do the same thing, some of my rows have only one entry and ...
1
vote
1answer
20 views

Filtering a protected column

I'm trying to filter a protected column in a spreadsheet that I shared with other users. But I it looks like I can protect it or filter it with the other user but not both. In Excel I can do it ...
1
vote
1answer
16 views

How do you reference a cell containing a date in a Query?

I have a table to keep track of hours put in working on an Interior Design project. The table looks something like this: 8/16/2015 6:30:00 PM 8:00:00 PM 1.50 Floorplan 8/19/2015 10:00:00 ...
1
vote
1answer
45 views

Google Sheets Customize View Using Create Custom Menu and Hiding/Showing Specifc Columns

Trying to determine a way that I can use a custom menu (.createMenu) with views that will allow me to show / hide specific columns. I need the capability to pick specific multiple columns and not ...
2
votes
1answer
13 views

View and edit data in Google Spreadsheets in a form

Question: Is there any way to view and edit data in Google Spreadsheets in a form, like Microsoft Access or OpenOffice Base can do that? It would be perfect if you could cycle through each record, ...
1
vote
1answer
26 views

Calculated field in Google Sheets not working

I have a pivot table created in a Google Sheet that looks like this: Desktop | Mobile Sessions | Trans. | Sessions | Trans. | 250 | 34 | 150 | 25 | .... | ... | ...
1
vote
1answer
23 views

Report/process only using the last entry made by the user in a Google form

I have a form where students can enter choices: for instance the specialisation they want to pursue, and the semester when they will start that specialisation. An example entry form is here, but it ...
0
votes
1answer
29 views

Google Sheets How to Hide or Show Specific Columns using Custom Menu [duplicate]

I've figured out how to create a custom menu, but I'd like to be able to hide or show certain columns (not necessarily ranges), based on what menu item is selected. Not sure on the next steps / can't ...
2
votes
1answer
30 views

Pull in analytics and click stats for goo.gl links in Google Sheets?

I've used this guide to create a Google sheet that creates shortened goo.gl links - is there any way I can update this to show the click stats in the next column?
3
votes
1answer
22 views

Comparing times on Google spreadsheet

I want to check if a set of fields indicate times of the day that are before 11am. The dates are stored in this format: 8:42:00 AM I tried A1<"11:00:00 AM" but it always returns True, even if it ...
2
votes
1answer
20 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
0
votes
0answers
15 views

Google Sheets: How to print information of user that is printing

Well, I am creating a sheet on Google Sheets and I will share it with some people. The users online can see its contents, but, to maintain the data update information when someone prints that, I ...
1
vote
0answers
8 views

Linking a photo to my Google sheet or a link to My Google Drive

I am building a catalog of products for our business, I would like to have an option where as you roll over the item a photo of the product will appear, in the same way 'add note' works but with a ...
2
votes
1answer
28 views

Division in Google Spreadsheets

How do I accomplish the following: I have a cell C5 with various numerical values. If C5 is less than 30 return 0 in C6 If C5 is greater than 30 return 1hr in C6 if C5 is greater than 60 return 2hr ...
1
vote
0answers
12 views

ArrayFormula changes the result [duplicate]

The following formula returns the correct result: =if(S3,if(J3>=NRealGames,0,min(max(NRealGames-J3,0),S3)/S3),) This one returns the same result: ...
0
votes
0answers
14 views

How to have auto calculation on Google docs invoice

I have this spreadsheet here but I have to do it manually each time and it gets a bit of a chore with working out 20% VAT on the days and then to add all the totals together. Can someone explain how I ...
0
votes
1answer
33 views

Need Help With a Script to Automatically Insert Rows in a Sheet Based on Other Rows/ Data Inserted in Another Sheet

I have a sheet titled 'Raw_Data' that I insert data into every day by inserting new rows above the data from the last day. I have another sheet titled 'Overall_Formulas' that populates formulas based ...
1
vote
1answer
19 views

Is it possible in Google Sheets to generate an email address based on data from two different cells?

I'm creating a database of individuals in a company in Google Sheets. I'm collecting the persons email address, first name, last name all in different columns. The email addresses for all these people ...
2
votes
1answer
24 views

Best way to iterate through all input arguments?

I am writing a custom function in sheets that will not have a defined number or format of arguments. As an example, the call might be: =myFunc(a1:b3,a2:a4,b1:b9,c1,d2, [might be more, might be less]) ...
2
votes
1answer
22 views

ImportXML with XPath does not return an entry of a table

Using Google Sheets with IMPORTXML to scrape a piece of information from a website via XPath. Here's the site (click here). The specific piece of information I'm trying to extract is the Price/Sales ...
3
votes
1answer
34 views

Color every other row in filter view

In Google Spreadsheets, I can use conditional formatting to color every other row in a table a certain color to make looking at wide rows easier. My problem is that I also want to use filter views so ...
1
vote
0answers
15 views

How do I stop Google Sheet to assume the saved values in column as equation?

So I have bunch of data in the format of [Val1 Val2] in a same cell (not the best way to save, but it just came that way when I got it) such that I have two values like [1.01 2.01] in same column A. ...
2
votes
1answer
18 views

How to perform a Vlookup with search column dynamic depending on input in another cell

I have the following table. What I want to do is as follows. User selects either Minor, Medium, or Major and that specifies which column is used. User then inputs a number 1-100 and the chart ...
1
vote
1answer
26 views

Tie Google Sheets cell updating formula to script that runs on demand?

I have a Google Sheet with about 300 rows in it. A couple of the columns have formulas like this: =IF(ISBLANK(E210),C10,QUERY(importrange(SheetKey,"SheetName!A:B"),"select Col2 where ...
1
vote
1answer
23 views

Display most recent comments from column connected to date and username in Google Sheets

In Google Sheets I have three columns: Timestamp, Username and Comment. How can I search a column for the most recent comment of a username and overwrite the most recent comment, in another cell? ...
0
votes
0answers
22 views

Set color if cell value is element of another sheets row [duplicate]

I am trying to figure this out since a while now. I have got it working if everything is in the same sheet! It goes like this: Select row K -> conditional formatting -> Custom formula is ...
0
votes
0answers
12 views

Google Sheet Cell to have Clock to Pick the Time

How do I make a cell show a clock to pick / click on a time from a clock face to then add that time to a cell without having to type the time value into the cell.
3
votes
1answer
29 views

How does one remove a new line in a cell in a Google Spreadsheet?

I frequently have a problem when entering a row of data that one or more cells will get a spurious newline at the end of their data, and that row will become double-tall. Sometimes I can guess the ...
6
votes
2answers
235 views

How can I find duplicates in a column, using formulas?

Has anybody come up with a formula to find duplicates in a column? For example: a b c d b d =somefunction(A1:A6) Output: b d I know there is a =unique() function, it's basically a ...
0
votes
0answers
8 views

How to detect previously deleted Google Calendar events

Per this article, Google Calendar events are not really immediately deleted. Therefore, previously deleted events are successfully returned by the methodcalendar.getEventSeriesById(eventId) I need ...
3
votes
1answer
41 views

Course allocation with array formula in Google spreadsheet using constraints

I try to assign students to courses based on the student's preference for each course and course capacity. Students are ranked based previous performance. In the current implementation, the data is ...
5
votes
1answer
46 views

Enlarge label area in Google Spreadsheets

Is there any way to enlarge the label area in Google Spreadsheets? E.g. see red rectangle: I would like to increase the label area so that the label text don't get cropped.
1
vote
1answer
22 views

Add date stamp when new row added by Zapier

I am trying to add a submission date to column 1 whenever a new row is automatically added through Zapier. Is this possible? E.g., C1 C2 C3 (DATE STAMP ...
2
votes
2answers
23 views

onOpen doesn't execute

I've got a google spreadsheet I created yesterday and used Tools -> Script Editor to create a function in a single file called Code.gs function onOpen(e) { var email = ...