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

learn more… | top users | synonyms (2)

0
votes
0answers
2 views

Google Sheets formula for great than one # but less than another

I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values. Example: If value is between 60 and 69.9 return "D" If value is between 70 and 79.9 ...
0
votes
0answers
2 views

Checking if a cell's contents matches an entry in a list of data

I have a google spreadsheet with one tab (countries) containing a table with a list of countries (i.e. France) and their associated IDs (i.e. 21). On a separate sheet (regions), I have a table with ...
2
votes
1answer
19 views

How to add bulleted list in Google Sheets

I am unable to add bullet in front of any sentence. See below example, I need circle bullets in front of every sentence written in one cell. First Sentence Second Sentence Third Sentence How can ...
2
votes
1answer
20 views

Parse out column with commas in it into separate rows

I have data in the following 2 column format in my google spreadsheet: 3M 701 A.W. Sperry DSA-2002 Accusplit 725MX, 705X, Magnum XL I would like to be able to parse out the 2nd ...
2
votes
2answers
31 views

Truncating the Title in the HYPERLINK function in Google Spreadsheet

I have a number of URLs that I first convert from text to the HYPERLINK function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached ...
-2
votes
1answer
17 views

Lock a column based on the date

I have an Employee attendance register in Google Sheets, wherein I want to lock the particular column/s after the date is passed over. This feature is required for the clerk to enter the date on the ...
1
vote
1answer
17 views

Google Sheets - Conditional Formatting Based on Date Formula

I'm trying to highlight the contents of a Date cell if the following conditions are met: Value of another cell = "No"; and Value of target cell + 7 days > Today The formula that I have right now ...
1
vote
1answer
24 views

Using a query to pull data for today

I am trying to pull the data from sheet 1, using the following formula: =query(Sheet1!A1:V1850,"select I,A,D,E,F,H,R,S,L, M,O where I = 'TODAY()'") So I do not have to enter todays date every time. ...
3
votes
1answer
21 views

Finding the average of top 10 smallest values in Google Sheets

I'm looking for a formula to find the average of the top 10 smallest values within a dataset. It works in Excel but not in Google Sheets. E.g. (that works in Excel): ...
0
votes
0answers
10 views

Bound Scripts in Google Sheets on iPad and Android Apps [duplicate]

I created a Google Sheet with a bound app script on my desktop but the scripts do not run on the iPad or Android apps for Google Sheets. Does anyone know if this can be done?
2
votes
2answers
32 views

Join several columns with a single formula returning an array

This is the opposite of my question Split a column of strings with a single formula returning an array. I have several columns, say, A-K, which I want to join into one comma-and-space separated column ...
3
votes
2answers
31 views

Pick the first value in every row that has not been picked yet

In the example I am giving below, I would like the result in Column G to be what is in Column C, unless it already exists in Column G in rows above it. These rows represent students' choices for ...
2
votes
1answer
54 views

Print several non-consecutive columns of a Google Spreadsheet using a script

I have script for print. I need print range without some column (e.g. print just B,C,D,E,G and miss F) and without all notes in sheet. The script below prints all the columns. function printPdf() { ...
1
vote
1answer
12 views

How can I get the halfway date of two dates in Google Sheets?

I have two date columns in mm-yy format and I need to know the halfway date of these two. What formula do I use? I tried simple subtraction of the two dates and then divided it by two but it didn't ...
1
vote
2answers
38 views

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
3
votes
1answer
18 views

How to shrink column when wider than the window?

I double clicked the column border so it expanded wider than the window. When I scroll to the left it jumps, so I can't see the previous column. How do I shrink it down?
3
votes
1answer
18 views

Union of two lists of key-value pairs

In a spreadsheet I have two lists (key-value pairs basically) that look like this: [A] [B] [D] [E] a 23 b hello b 42 d world c 11 f good f 17 g bye h ...
3
votes
1answer
36 views

Show URL used to edit responses from a Google Form in a Google spreadsheet by using a script

I'm trying to make the edit URL for a google form automatically populate in the response google sheet that it's attached to. I've seen this already and want to use it, but i'm having issues trying to ...
3
votes
1answer
35 views

Timestamp in two columns

As a teacher, I am trying to create a Google sheet for the students to use as they sign in and out of class. I have successfully used code to auto insert a time stamp into column A if Column C is ...
0
votes
1answer
16 views

Copy and paste information once a day form a Google spreadsheet

I have a Google spreadsheet that is being modified during the day, and need to copy and paste the final information (at 8 pm) in a different tab, each day, pasting the new information under the old ...
0
votes
1answer
24 views

Can I cut and paste projects from a Google sheet using a script?

I want an automated method of cutting and pasting completed projects from one sheet to another sheet. I want to have a column where users can go in and type "DONE" at the end of a row to state that ...
1
vote
2answers
26 views

Create an OR conditional statement from a list of options

I want this: input in A1 and output in A2. input: 0 output: AND (A=0) input: 0,1 output: AND (A=0 OR A=1) input: 0,1,2 output: AND (A=0 OR A=1 OR A=2) etc... I've tried a few things with the REPT ...
1
vote
0answers
15 views

Sum the same cell in a range of sheets [duplicate]

I have an Excel spreadsheet that has this formula: =IF(ISBLANK(B6),"",SUM(Sheet1:Sheet10!O6)+Sheet11!P6+Sheet12!T6) I would like to use this spreadsheet in Google Sheets so I can get it on other ...
1
vote
1answer
20 views

How can I force a Google Spreadsheet to have column in one tab have the same width as the same column in a different tab?

Is there any way to force a Google Spreadsheet to have column i in tab j of same width as column i in tab k? In other words, can I configure Google Spreadsheet so that a column width change is always ...
1
vote
1answer
15 views

Time stamp on last row of column for changes in predetermined range

I am looking for a way to add a time stamp to the last row of a column of figures any time there is a change in the above range. I have been able to find a number of examples where you can timestamp ...
1
vote
1answer
18 views

Finding task with highest date

I have this sheet https://docs.google.com/spreadsheets/d/1X9tMWp0Is7BaTpJ2rGWUdLLBtxoCHxB3hqCJIXudg8g/pubhtml?gid=0&single=true where I want to do simple project planning and resource booking The ...
0
votes
1answer
13 views

Google sheets, how to grab a html link from a table

http://www.nasdaq.com/markets/ipos/activity.aspx?tab=upcoming under each of the company name is a html link to another page. How do I import that html link into Google sheets?
2
votes
2answers
24 views

How to recombine text into one line in Google Sheets

=index(ImportXml("https://www.google.com/finance?q=ibm", "//div[@class = 'companySummary']" ),0,1) Put that anywhere in a Google sheet and it will give you a cell with a block of text. This text is ...
2
votes
0answers
22 views

Map Chart using decimal degrees in Google Sheets

I have a column with decimal degrees (locations) and another one with values. Can I plot a Map Chart on Google Sheets (with markers)? If I have to use the Script Editor, any existing (working) ...
1
vote
1answer
19 views

How to convert text of the form Month-Year to date format?

How do I convert the text "Oct-14" to date, so that it's being read as "Oct 01, 2014. I tried to go to Format > Date and selected the format: Oct-2014, but google sheets is interpreting it as "Oct 14, ...
1
vote
2answers
106 views

How to calculate the percentage of a date in the same year in a Google Spreadsheet

Given a date, I need to calculate the percentage of its position in the year. So that 31-12-2015 would return 1 or 100%, because it's the 365th day of 365 days in that year. And 1-1-2015 would return ...
1
vote
1answer
18 views

Remove any sheets not listed in an array

I have a Google App Script that has 6 sheets. onOpen, I would like to see if the user created any additional sheets, and if so, delete them. So far, I have: function onOpen(){ var ...
0
votes
2answers
41 views

Which factor will give most speedup to every update to a large Google Spreadsheet?

I have a huge spreadsheet in Google Spreadsheets, and it takes about half a minute to complete every change I make to its contents: UI stays responsive, but there's a slow progress bar showing the ...
0
votes
1answer
18 views

Count number of times a term appears in an array of lists

I have a question the count function. Lets say a have a single column of data coming in from a google form. Its going to have 3 data inputs coming in from each response of the form. The possible ...
1
vote
1answer
19 views

How to get most frequent terms from a set of lists?

I have a list on Google Spreadsheets. Each line is a mission, and one of the columns indicates who I've worked together on the mission (which is between 1 and 3 people). The format is "Aquaman, ...
3
votes
1answer
38 views

How can I create a checklist in Google Keep from a Google Sheets spreadsheet?

I exported my list of books I own from Shelfari, and want to create checklists in Keep for certain authors that will make it easier to browse second hand stores and not buy things I already have. I ...
0
votes
2answers
45 views

Date format in Google Sheets

I have a Google spreadsheet file and I need to convert the date column from this format: Sun, May 4, 2014 4:30PM to this format: mm/dd/yyyy. Basically, I need the date to be in a simple format and ...
2
votes
1answer
27 views

How to list which values from field A don't appear in field B? (Survey recipient responses)

I have a weekly survey I send out to the same list of people using Google Forms, and they populate a 'responses' spreadsheet. I delete the responses weekly, and new rows are added as new responses are ...
3
votes
1answer
41 views

How do I create a data validation function to print the values of a specified column across all pages?

I have a specific task that I believe might require some kind of custom script. Here is (a copy) of the spreadsheet in question: ...
1
vote
1answer
37 views

Having trouble getting rating sheet to update as games are entered

I keep a spreadsheet that uses the elo equation for chess where I work that updates players ratings when I enter the two players names and who won or lost (1 for win, 0 for loss). My issue is that it ...
2
votes
1answer
48 views

Getting COUNTIFS to work in complicated Google Sheets formula

I'm having trouble with COUNTIFS on Google Sheets. I've been able to use SUMIFS and now I need to use COUNTIFS. I tried to do a Find and Replace, changing SUMIFS to COUNTIFS, on what I thought were ...
2
votes
0answers
32 views

IFTTT to Google Drive (Sheets) - How to write on the same row?

I'm looking for a way to get a recipe from IFTTT to Google drive (sheets) in the same row. The recipe writes in the first 3 columns, but if there is anything already in the same row it skips that row ...
1
vote
1answer
33 views

Restricted view of a sheet, with each person seeing only their rows

I created a Google Spreadsheet. Column B contains the name of my employees. In Column C there is a task for every employee. Different employees need to have access to this document, but I prefer my ...
2
votes
1answer
37 views

IMPORTHTML at a certain time of day in Google Spreadsheets

I am looking for a way to import the table from this formula once per day at a certain time instead of automatically updating as the formulas seems to do. I need to do this in order to track certain ...
2
votes
0answers
15 views

Coordinates of an external named reference

In a spreadsheet, I've got a column named range: Sheet1!I:I, named NamedRanged1. In another sheet, I'd like to get the index of that named range, i.e., 9 (I is 9th column). I tried with ADDRESS and ...
4
votes
0answers
37 views

Copying data to *and from* another sheet based on the month of a date

This is a follow-up to Copying data to another sheet based on the month of a date, with a different requirement: instead of one-way data transfer (from Master sheet to others), two-directional ...
0
votes
0answers
25 views

How do I print a Google Docs document directly from Firefox?

Using Google Docs/Spreadsheet. If I open a Google Doc and select print (either via the menu, ctrl+p, or toolbar icon) from within Chrome or Opera it then opens a print preview screen where I can ...
6
votes
2answers
45 views

How to use formula values present in another cell in a formula

I am using the formula from this question =ArrayFormula(sum(sumif(A:A,{"Bus";"Train"},B:B))) I am planning to use it for multiple categories. For example the above can a sum of my travel category ...
3
votes
1answer
20 views

Copying a cell with partial formatting of text

I have a cell in Google Sheets with formatted text content. Something like this: You must complete both parts of the assignment. If I copy that cell into another cell, the bold formatting is ...
4
votes
1answer
27 views

Copying data to another sheet based on the month of a date

I hope someone can assist me in looking in the right direction. I have a sheet (Master) which has rows of data regarding different types of projects and deliverables. One column in this sheet has ...