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

learn more… | top users | synonyms (2)

1
vote
1answer
58 views

Find in range and go to cell. Trigger On form submit not working!

I am using a script that checks column range B2:B for a specific value contained in a cell of another column (the value in this cell deliberately corresponds to the last value in the column B) and ...
2
votes
1answer
95 views

Periodically updating a URL in the ImportXML command with a script

I am using importxml to extract prices from multiple websites. My main concern is to be able to refresh the prices whenever I want and not be dependent on Google's hourly update. I am using this ...
2
votes
1answer
72 views

Google drive (sheets) shared file - move ownership - different domains

I'm helping my daughter with her project and started an example of how to create a table using google sheets, which I shared with her. She continued on with it but now I want to remove it from my ...
1
vote
1answer
317 views

How can one auto create pre-filled URLs with registered addresses from a roster

We use google forms to send out tests to our students. We have a spreadsheet with rosters where we keep the info of the students (email, birthday, gender, guardian etc) for every class in our school. ...
2
votes
1answer
35 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
0
votes
0answers
59 views

Can I filter or query an importrange in a single formula without importing the range multiple times or using a 2nd worksheet?

Title sums it up, I want to be able to filter an importrange in a single formula, can this be done without importing the range multiple timer in the filter? I want to do this without using a 2nd ...
2
votes
1answer
47 views

How to auto format a phone number with an associated hyperlink in Google Sheets

I don't know if this is possible, as its a bit of an edge case, but it would be amazing if this can be accomplished. I have a spreadsheet of contact information, with fax numbers. At work, we send ...
0
votes
1answer
28 views

Google Spreadsheet Query blanks out text cells in the query range

When I perform a query to essentially filter a range of names, and times, any cell that is not a time gets blanked out. Screenshot of example: I'm keeping this post fairly short since the image ...
2
votes
1answer
52 views

Google Spreadsheet Conditional Formatting depending on up to 7 Fields

I'm trying to color a column depending on the row. My table looks like this: the only thing i managed to do was =NOT(ISBLANK(C101:C104)) and set it to orange using the conditional formatting. I ...
3
votes
1answer
30 views

How to edit dates in Google Sheets as ISO 8601?

I know how to make Google Sheets display dates in ISO 8601 format (YYYY-MM-DD). Now, how do I make them not only display, but edit in the same format? See the edit line below: Moreover, if that ...
0
votes
0answers
33 views

Your Time stamp code

I have copied and pasted your code to put a time stamp on a spreadsheet that I am working on and it is working great. Except the day is coming back as "56" for some reason and I don't know why. It ...
2
votes
1answer
42 views

How to update values from another cell only once, not with subsequent edits?

I have a cell A1, whose values I change periodically. I have a cell F7:F, I add values to this column from time to time. I have another cell K7:K whose values depend on what's in A1 and what's in F7:...
4
votes
2answers
61 views

Using two values from one column to fill another column and repeat the calculation

I'm trying to work out how far I travel on a tank of petrol. I've set a column to display the mileage at the time I enter the petrol station, and I want to display the difference between the two ...
3
votes
2answers
49 views

How can social hashtags be extracted from a cell?

Example: A2 -> What a beautiful day! #sunday #summer B3 -> #news Stay tuned for our next contest! #something Desired output on B2 ->#sunday #summer Desired output on B3 ->#news #...
2
votes
0answers
29 views

Updating/merging rows from imported data that share the same primary key in Google Sheets [duplicate]

I'm using an IMPORTDATA function tied to a auto-running script to pull in a .TSV data file from an obscure webform system. That all works well, and I'm able to generate a nice dashboard from this data....
1
vote
0answers
53 views

Identifying row records in different tables when scraping through ImportHTML query in Google Spreadsheet

I'm inserting two different tables successfully with ImportHTML. However I want to identify records in rows to which table it belongs. In this particular case (link attached) the player Higuain plays ...
2
votes
1answer
21 views

Find slope/intercept of last 30 values entered?

I have a column A7:A that has numbers from 1+; I have another column B7:B, that has dates from Oct 31st, 2015 +; I have a column F7:F that has weight values; I have a goal weight of 108.36297798; ...
3
votes
1answer
95 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 for....
1
vote
1answer
48 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
78 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
39 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
82 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
75 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
56 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
46 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
96 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", IF(...
3
votes
1answer
55 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
56 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
36 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
52 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
87 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
57 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
51 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
46 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
66 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
58 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
68 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
48 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 '"&TEXT(B$...
2
votes
2answers
167 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 ...
2
votes
1answer
149 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
41 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 | 2016/01/...
1
vote
1answer
95 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
56 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
160 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
52 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
24 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 tab?...
5
votes
1answer
96 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
40 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
26 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
54 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 ...