A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

2
votes
1answer
52 views

Changing colour of row if a certain aspect is met

I have a list of clients that send in certain information to me and I have setup columns to enter a tick using (=char(10004)) on R,S,T,U,V,W,X & Y columns and I need to colour them to go green ...
2
votes
1answer
1k 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 ...
2
votes
1answer
49 views

Highlighting duplicate data in rows

I have a query and I cant figure out why it's not correct. I have two lists of data and I want to highlight the matching data in the separate lists. Below is the query I have constructed =IF(...
2
votes
2answers
623 views

How to reference a fixed sheet name in Google Spreadsheet

I am doing reports from a set of data in Google Spreadsheet. As of now, I have several sheets, among them Raw Data and Processed Data. In my Processed Data sheet, I use formulas such as: SUM('Raw ...
2
votes
1answer
86 views

Customizing filter in Google Spreadsheet

I'm trying to create a Spreadsheet for work purposes but I can't get it the way I want. If I do it in Excel it works but I can't match it in Google Spreadsheet. I don't even know if it's possible. (...
2
votes
1answer
62 views

Google Sheets “Skipping blanks”

I have a huge data sheet that is being updated every week. It has about 2,000 colums and 6 rows. Every cell each week is new (Its a list of average prices, so the names of the items remain the same ...
2
votes
1answer
60 views

substitute() multiple terms in Google Spreadsheets

I'm trying to simplify semi-complex text strings into much simpler ones - specifically names into hashtags. For example: Tomáš Rosický into #TomasRosicky or Alex Oxlade-Chamberlain into #...
2
votes
1answer
103 views

Using HLOOKUP for date values

I'm trying to get the HLOOKUP function working in my sheet but it's not working like it should. The function is: =HLOOKUP(TEXT(NOW(), "yyyy-MM-dd"), INDIRECT(TEXT(NOW(), "MMMM") & "!B1:AE5"), 3, ...
2
votes
1answer
55 views

How to automatically increase the exponent in Google Spreadsheet columns?

I have asked a similar question before How to automatically increase the exponent in Google Spreadsheet? But the new question is: In a Google Spreadsheet, I put in the following numbers in a ROW, ...
2
votes
1answer
739 views

Copy-pasting cells with conditional formatting

I'm trying to use conditional formatting in the goals area. Currently the calorie goal (J10) turns green as long as I stay under it's number, and it's compared to the total calories for the day (J3). ...
2
votes
1answer
83 views

How to convert all numbers regardless of decimal separator into valid number of current locale in google sheet

I have a column with text looking like this: 123,123 123.123 123,123 123.123 I would like another column to parse this into decimal numbers, disregarding the different decimal separators (dot . and ...
2
votes
1answer
131 views

Same x-axis for multiple graphs in same plot window

I am trying to do exactly what the answer to this other question explains (I have several sets of data to be plotted in one graph with the same x-and y-axis); but with one difference: I don't have ...
2
votes
1answer
2k views

How to make Google Sheet jump to today's row when opened?

I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened.
2
votes
1answer
181 views

Auto Sort data in Google sheet, but by sheet not range

I have found this script from this question to auto sort a column: How can I apply a script to only one specific sheet within a spreadsheet function onEdit(){ var sh0 = SpreadsheetApp....
2
votes
1answer
543 views

Importing columns across two Google Sheets in same spreadsheet

I have one spreadsheet which houses the database of my members. In the same spreadsheet, I have another sheet where I will track attendance for the events for each of these members. First issue: Now,...
2
votes
1answer
611 views

Trigger Script in Spreadsheet in Similar Fashion to a Link

I currently have a button on my spreadsheet (a text box image that triggers the script). Unfortunately this button doesn't work when using a tablet. Is there a way of triggering a script to run via a ...
2
votes
1answer
733 views

Script to create a new worksheet in Google Spreadsheet

I want to be able to have a button on a Google Spreadsheet. The user clicks on the button and a new worksheet is created based on a template (or blank existing worksheet). The new worksheet should ...
2
votes
1answer
73 views

Need to be able to refer to a sheet within a count function

I have a Google Spreadsheet I'm working on. Each tab has new data that's pasted in on a weekly basis, for example, I just created a new sheet called 5/5/2015 and included the data within. What I'd ...
2
votes
1answer
81 views

How to sort Google Sheets columns by row height

I'm working on a sheet that involves multiple columns corresponding to various data. Each column has a varying number of cells of varying heights with wrapped text. What I want to do is make the sheet ...
2
votes
1answer
75 views

Forcibly format column as Date in sheets onOpen() with Javascript

I have a simple sheet that gets some date data submitted via a form. The form works great, data is collected, but I have to click on the column, and do Format → Number → Date in order to get dates to ...
2
votes
1answer
34 views

How to consolidate information from different sources in Google Sheets

So I have a Google Sheet with several sheets (tabs) in it, like so: Sheet 1 - Weapons Legend A B C D A = Date 5/3 John Sword 10 B = From 6/3 ...
2
votes
1answer
41 views

How to apply formula to multiple cells without it adjusting part of the formula

I'm having a little bit of a problem and was wondering if your expert knowledge could help me out. Formula in question: =if(and(E2=I1,F2<=J1),K1,if(and(E2=I1,F2>J1),K2,if(E2=I3,indirect("K"&...
2
votes
1answer
43 views

Adding an overtime column to a Google Sheets template that includes the overtime rate in the total

I would like to use this template to log bi-weekly hours. It is almost perfect except that there is no column for overtime hours. How would I add an overtime hours logging column that would add the ...
2
votes
1answer
27 views

How to work out the price of shipping an item in Google Spreadsheets

I'm currently trying to build a formula to work out a problem I have. What is the problem: Right now before I can send an item out, I need to work out how much that will cost me. This is done by ...
2
votes
1answer
30 views

Calling a date with =concatenate is resolving to strange number

Using: =concatenate("Review Check on ", B2) B2 is "4/20/2015", that I created using the date insert shortcut (command+; on Mac). The final cell resolves to "Review Check on 42114"—the “/”s have been ...
2
votes
1answer
129 views

Google Spreadsheet limit ArrayFormula

I'm using the ArrayFormula to calculate multiple ranges and it's slow because it's trying to calculate everything in that range, even though it has nothing in it. Is there a way I can limit the ...
2
votes
1answer
67 views

Make cell height in one column not affect other columns within the same row in Google Spreadsheets

I have done a schedule in Google Spreadsheet. I set text wrapping to wrap. The problem is that when I write something in column A and the row in column A gets expanded to fit all text all other ...
2
votes
1answer
35 views

Print text associated with a value

I have a Google Sheet that collects data and analyses any trends. What I'm trying to do is figure out how to output the text value associated with the numerical value. Example: Apples 7 ...
2
votes
1answer
116 views

Creating multiple time stamps on google sheets to make dependent drop-down boxes work

I read so many forums but cant find an answer to what is likely a simple matter. I need Time Stamps on 2 different sheets to make 2 different Dependent Drop Boxes work. I use the script below but can ...
2
votes
1answer
53 views

Apply formula when other is modified

I´m trying to create a spreadsheet to manage a business, in particular I want to use it to register the sales of the day. The problem i´m having in particular is that I need it to copy the price of ...
2
votes
1answer
73 views

Find first value in list in cell

I keep running into situations where I have a list of values in a cell in Google Spreadsheets, and I need some value out of the list: by list I mean a string of values (generally dates, but could be ...
2
votes
1answer
32 views

Is there any option to operate with 2 lists in one list in Google Sheets?

Is it possible in Google spreadsheets to have one list which is somehow divided into 2 parts and both of those parts consist of different lists? Something like iframes in HTML.
2
votes
1answer
151 views

Google sheet multiple column lookup

I have a table that looks like this: A | B | C ---|--------|--- 10 | Item x | 1 10 | Item y | 1 10 | Total | 2 20 | Item x | 4 20 | Item z | 5 20 | Total | 9 Now I would like to perform a ...
2
votes
1answer
222 views

How to embed a Google Spreadsheet with just cells and a sheet list?

This answer shows how to hide the UI and sheet list by appending rm=minimal to the URL. Is there a way to hide the UI but not the sheet list? I want to insert the spreadsheet on another page with an ...
2
votes
1answer
642 views

Unique ID Column

I have a Google Spreadsheet that I want to have a unique number created in column A when I enter information in column B. Below is the formula I currently have that works, but I am wondering if there ...
2
votes
1answer
257 views

Import a CSV file from a URL to Google Sheets and update it periodically

I'm looking to pull data from a CSV file that gets update regularly into a Google spreadsheet that I can format the way I want. I know how to import the CSV as a one time thing but I don't know how to ...
2
votes
1answer
569 views

How do I loop a script through all pages but the first one?

I'm very new to all this. I'm trying to write a script that sorts sheets by descending date order each time a new row is entered. I need the script to run on all pages but the first (the summary page)....
2
votes
1answer
32 views

Importing a value from a webpage, and storing it as value, not a function

I am writing a spreadsheet that imports values from webpages that change a lot, I want to keep note on these values over the days of a week. However I'm unsure of how to note them at certain times. ...
2
votes
1answer
100 views

Calculate total based on drop down criteria

I am creating a budget that has different expenses on one worksheet (Expense_Overview). On another worksheet, I have a list of expenses (Expense_Detail) I am entering that are categorized based on a ...
2
votes
1answer
54 views

Automatically creating sheets across different Google Spreadsheets

I have one spreadsheet that collects the Google form data that is submitted. I then created spreadsheets titled "A-D", "E-H", "I-L", and so on to create the alphabet. I would like students to use ...
2
votes
1answer
389 views

Google Sheets importrange every x hours

I want to link 2 spreadsheets with importrange let's say A with B. B is the source. I want A to import numbers from B every 6 hours, because the numbers in B are constantly moving and since the sheet ...
2
votes
1answer
1k views

How to generate a random string?

I need to create a random string of 8 characters for every row in Google Spreadsheet. Basically string should contain letters and numbers upper and low case only, example: LZJ2cuWT Any idea how to ...
2
votes
1answer
202 views

How do I get a weekday 12 weeks ago using in a Google script?

I'm having the following formula: =TODAY()-WEEKDAY(TODAY(),3)-84 and this gives me Monday, 12 weeks ago. I'm working on this code, but it gives me only a certain days back var twelweWeekAgo = new ...
2
votes
1answer
389 views

Google spreadsheet formula using If() And() Or() Statement

I need help with a Google spreadsheet formula. This is what I'm trying to do: If (A3 = X) and C3 is not any of the following choices in this list ("Ready for QA Testing on Stage","Deployed to ...
2
votes
1answer
99 views

Formula breaks subtly in ARRAYFORMULA in Google Sheets

I have a spreadsheet document with a formula comparing a list of URLs to lists on two other sheets within the document. I desire to know if each URL on the first list is present on the blocklist, ...
2
votes
1answer
161 views

How to grant a specific list of user to access Google Form?

Currently when I click on the "Send Form" button in Google Form and user will be able to input the form when they know the URL. Is it possible to grant a specific user to have access to the form? (So ...
2
votes
2answers
546 views

Conveniently export part of Google spreadsheet to HTML page

Google spreadsheet allows you to save table as HTML: The problem is that what the HTML you get is not publishable in website or coherent HTML document: I'd just like a part of the table as HTML. ...
2
votes
1answer
44 views

Select range by typing it in, e.g.,“f1:f600”

How do I select ranges by typing in the range value in Google Spreadsheets? I need to be able to select, say, d3:d1000, by typing in “d3:d1000” somewhere. In other words, what F5 does in Excel. I ...
2
votes
1answer
2k views

How to set Custom data validation in Google Sheets (A really simple step-by-step guide needed)

I'm trying to figure out how to create custom data validation in Google Sheets what are the steps I need to follow to achieve this? For example if I wanted the following validation: // Set the data ...
2
votes
1answer
60 views

Collect data from dropdown list

I'm making an expense spreadsheet, to keep track of all my expenses. I have made a dropdown-list with different category (Rent, Food, Others etc.) these are put into C2 to CXX, in the next cell D2 to ...