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

learn more… | top users | synonyms (2)

2
votes
1answer
16 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
19 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
23 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
16 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
23 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. ...
2
votes
1answer
13 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?
1
vote
2answers
27 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
51 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
36 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
34 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
34 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
14 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
35 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
31 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
36 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
14 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
26 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 ...
7
votes
3answers
286 views

How to include multiple IF statements in one cell in Google sheets

I am attempting to change the value of a cell in Google Sheets based on the value of an adjacent cell. This adjacent cell gets its value from the colour of the cell adjacent to it using the formula ...
3
votes
2answers
41 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...