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

learn more… | top users | synonyms (2)

3
votes
1answer
76 views

How to get value of adjacent cell based on key, if the table is split into multiple columns

I need some help figuring out some Google Sheets function. I'm trying to look for the value of an adjacent cell based on a key. The problem is that the table has several rows and columns to search ...
1
vote
1answer
32 views

How can I automatically set “sheets last updated” in a cell on Google Docs Spreadsheets?

Simple task!.. output a date and time every time something in the sheet is edited! function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets("Data"); var ...
1
vote
1answer
65 views

How to embed an image inside a Google Document comment?

I add comments all the time to Google Documents, however i would like to embed images inside Google Document comments (similar to how you can add images to facebook comments). Is this possible and if ...
1
vote
1answer
36 views

Conditionally format data in spreadsheet column, but only when it changes

Say I have this data in a Google Spreadsheet: cat cat mouse mouse mouse cat dog dog And I only want to apply conditional formatting to the ones in brackets [] (so I can hide them but still ...
4
votes
2answers
37 views

Formula based on cell positions from target cell

I have a spreadsheet that I use to keep track of my paid time off (PTO). I accrue a certain number of hours each pay period (which I know in advance). When I take some time off, I add a row and keep a ...
3
votes
1answer
61 views

Google sheets MM-DD-YYYY to DD-MM-YYYY

I downloaded CSV data from my Etsy shop to calculate taxes. I'd like to have the date "copied" properly. The CSV date I get from Etsy is formatted as "02/09/16" This is "2 September 2016" in Dutch ...
2
votes
1answer
36 views

Auto Sort Google Sheet with onEdit function

The following function successfully sorts my file (containing 3 sheets) individually. When I hit the "play" button on the script, it properly sorts (as expected) the current (active) sheet. Since my ...
3
votes
1answer
43 views

Alternate Background Colours, Conditional Formatting Based on Date?

I have a date column, A7:A, I wanted rows to be coloured every other week, A7:A may have duplicate dates but I want all the dates that lie on the same week to be one colour and alternate so it's ...
3
votes
1answer
40 views

SUM function not working for cells with formulas in Google Sheets

A B ---------------------- TOTAL =SUM(B3:B) Entry1 =IF($A3="Entry1","20",IF($A3="Entry2","170", IF($A3="Entry3","10"))) Entry2 =IF($A4="Entry1","20",IF($A4="Entry2","170", ...
3
votes
1answer
38 views

Create a symmetric matrix in Google Sheets

So, I'm working on a project in Google Sheets that has me creating a symmetric matrix. I've filled one half of it with the correct values, but I'm not happy with the idea that I have to reinput the ...
3
votes
1answer
39 views

Can I set the number format to show exactly 2 decimals only when needed in Google Spreadsheet?

I want to set the number format as below: If the number have more than 2 decimals, round it to 2 only (e.g. 12.3456 -> 12.35). If the number is whole number, just let it be (e.g. 12 -> 12).
0
votes
1answer
33 views

How to sum up values from last Monday to Now?

I have a column that totals earning, B4:B, which sums up all values, however, I just want it to total values from last (most recent) Monday to Now, until it's Monday again, start over, the date column ...
2
votes
2answers
42 views

How to add current date to cell if the rest of the cells (or some cells) in that row are edited, but only the first time?

I have a column A4:A (heading = Date), and other columns after it B4:O. I want column A to fill in today's date automatically when I fill in any other columns (some of these B4:O maybe empty, but ...
3
votes
1answer
75 views

If my rows are sparse, is there an alternative representation?

I'm making a perk calculator. Architecturally I have a sheet with perks, and another sheet where the user can select a set of perks and can see in a "results" cell their total effect. Examples for ...
1
vote
1answer
28 views

Search text in all columns of multiple sheets

Given many texts to look for, I want to search all my sheets in all columns if there's at least one partial case insensitive match then return true (or 1 or the sheet's name or whatever) and false ...
1
vote
1answer
47 views

Is it possible to use the contents of a cell in a custom conditional formatting rule in Google Sheets?

For example, given the sheet below: I'd like the background colours of columns B-D to change automatically when I update the values in column A Is that possible? I'm looking for a solution that ...
0
votes
0answers
21 views

How can I format nodes in a Google Spreadsheets organization chart?

Google Charts allows users to format organization chart nodes using HTML. (Awesome.) I can't seem to find a way to format organization charts in Google Spreadsheets. I'd already be happy having a way ...
1
vote
0answers
47 views

How can I decrease IMPORTXML refresh interval to 10 mins?

I have a couple of IMPORTXML’s fetching the prices for products, and I want the sheet to update the prices every 10 mins. I tried now() and a functions script but to no avail. What is the best way to ...
3
votes
1answer
56 views

Is there a way randomly distribute Google Form lead submissions

Is there a way to randomly distribute leads using Google Forms? A lead enters their information and it's logged in a Google Sheet. The goal is to randomly distribute the leads to other sheets, ...
4
votes
1answer
49 views

Duplicate and rename sheet in one action on Google Sheets

I have a script which references the name of a sheet in Google Spreadsheets. When I duplicate a sheet with a cell that uses that function, the function is called right away and returns something like ...
1
vote
1answer
25 views

How do I use a query on a timestamp to filter down to “day of the week” and “hour”

Currently I am able to Query my data by the date in a timestamp but now I want to narrow it down to the hour and the day =COUNT(QUERY('Form Responses 1'!$A$2:$E, "Select A where A>=date ...
2
votes
1answer
67 views

How can I embed a chart from a Google Spreadsheet in a Google Doc?

I would like to embed the chart from a Google Docs spreadsheet into a Google Docs Document, so changes to the original will be reflected in the embedded version. I found a way to embed a static image ...
3
votes
0answers
44 views

Google Spreadsheets CSV Encoding not UTF-8?

I was assuming that Google spreadsheet exports UTF-8 encoded CSVs. However, this does not seem to be the case — my german Umlauts, etc. (äüöß, etc.) are not recognized, i.e. when opening the file in ...
0
votes
1answer
36 views

Insert today's date as a value in Google Sheets [duplicate]

I have a Google Sheets where I store customers details and the date of the last time I contacted or was contacted by this customer: Customer name | Last contact date Bob | ...
1
vote
1answer
47 views

Google Apps Script Exceeded maximum execution time

My code for an API pull has been timing out a lot lately. Here is my code (note that I have changed some variables for privacy): // SHEET DATA var ss = SpreadsheetApp.getActive(); var data_sheet = ...
1
vote
1answer
40 views

COUNTIF multiple columns not counting values matching both criterias

Sheet1 Country Activity1 Activity2 Spain 2 2 Spain 0 0 Italy 1 0 France 1 0 Germany 0 0 Italy 4 2 ...
1
vote
1answer
79 views

ImportXML formula no longer working

Here is the formula: =ImportXML("http://www.bloomberg.com/quote/7501:JP", "//div[@class='price-container down']//div[@class='price']") As of several days ago, this formula stopped working. Why ...
1
vote
1answer
33 views

Defining a custom order in a Google Sheet query?

I'm using a Query in Google Sheets to pull data from another sheet. That query looks something like this: =QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York'",1 ) What I'd like ...
1
vote
1answer
21 views

Apply formatting and formulas to another sheet?

I have a template tab that contains all formulas and formatting. Then one tab for each user. I know how to reference cell content with =template!A1. How to reference formula that reference current ...
5
votes
1answer
79 views

Probability Density Function of a Chi Distribution

I'm trying to write an extension for Google Sheets to add the CHIDIST() function as in Microsoft Excel and LibreOffice Calc. My current attempt does not produce the same results as the LibreOffice ...
2
votes
1answer
17 views

A spreadsheet formula to assign letter grades based on numeric ranges

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 ...
1
vote
1answer
25 views

Using a second table to determine the value of a cell in the first table

I have a Google Spreadsheet with table 1 'city, country, region'. On a separate tab there is table 2 'countries and regions'. What statement can populate the 'region' column in table 1, based on the ...
2
votes
1answer
32 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
24 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
55 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
36 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 ...
2
votes
1answer
31 views

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
33 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
40 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
45 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
52 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
78 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
15 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
445 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
19 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
29 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
252 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
54 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
27 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 ...