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

learn more… | top users | synonyms (2)

2
votes
1answer
35 views

Make google spreadsheet chart use tab+column name as the series header

I have a chart of two series, Sheet1!Foo and Sheet2!Foo. They are named in the chart the same way - Foo. How can I include the sheet name in the series name?
2
votes
0answers
729 views

Cannot retrieve data for some exchanges from Google Finance

Since last weekend, I encounter problems retrieving data from exchanges such as SGX and SHA. =GOOGLEFINANCE(SGX:ES3,changepct) =GOOGLEFINANCE(SHA:000001,changepct) The error message is; When ...
2
votes
0answers
80 views

Running inventory list that references a product key [closed]

So I'm trying to create a rudimentary inventory management system in Google Sheets. I want this inventory guide to keep track of my product inventory, as well as my packaging inventory. So if a ...
2
votes
0answers
88 views

How to set Audio alert in Google spreadsheet that triggers by a specific value?

Can I set an Audio alert in a Google spreadsheet cell that triggers whenever it changes a specific value? I want some thing similar to "beepnow" function which is using in MS Excel VBA.
2
votes
2answers
891 views

Query Select Where is not null vs <>'' not filtering properly

I have an issue with my query where I have numeric cells and text cells that if all are empty I do not want to retrieve them however if anyone of the cells are not empty I want to retrieve that row. ...
2
votes
0answers
83 views

Formatting Google Sheet Dates Inconsistent

I have a sheet where various data is entered through a form. Dates are picked on the form using the standard date picker provided by Google Forms. Some dates in a column I can change the date format ...
2
votes
0answers
96 views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) update

Thanks for a very useful script. Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) I am trying to do the same thing, some of my rows have only one entry and ...
2
votes
0answers
21 views

Toggle named range window with a button?

I would like to know if it would be possible to toggle the different features in sheets with a script. What I am trying to do is to basically create a button that will show the Named Ranges side ...
2
votes
0answers
15 views

Is it possible to reference a cell above with an ArrayFormula? [duplicate]

Here is an example situation which I would like to achieve using ArrayFormula: Column A is the data, column B is the calculation. Here is an implementation without ArrayFormula: Here is how I ...
2
votes
2answers
42 views

Format of spreadsheet chart not kept when exporting inside my GoogleApp

I built a Table Chart in Google Sheets, and all numerical values except for the last column are displayed as percentages, which is a behaviour I need and want. View of my sheet (sample of the Table ...
2
votes
1answer
305 views

SUMIF using NOT ISDATE

I want to sum across a range of fields, but only if the cell contains a number, not a Date. I would love to use something like SUMIF(A2:Z2,"!ISDATE()",A2:Z2) But the criteria in SUMIF is incredibly ...
2
votes
2answers
124 views

Google Spreadsheets: Insert new rows automatically

I have two columns, one with German texts segments (in rows) and the second one with the Spanish translations of these segments. The problem is that in many cases the segments are too long for my ...
2
votes
0answers
13 views

Google Sheets: how to custom number format based on value? [duplicate]

In Google Sheets, I want to format 1230000 as "1.23 M" and 1230 as "1.23 K". However, it seems I can only format either in terms of M, or K, not both. It's possible to set formatting conditions in ...
2
votes
0answers
125 views

I created a button in a panel (dialogue window), when I push the button I'd like to appear a new panel (dialogue) window

I created a button in Google Spreadsheet. When I push the button a custom window appears. In that window I have two buttons, one to close and one to see a new window. Everything goes well except the ...
2
votes
2answers
1k views

How to edit a particular sheet using Google Forms

I created a form that has list of items to be selected. I also created a spreadsheet that has a couple of sheets that correspond to the elements in the form. I wanted users to select a category and ...
2
votes
1answer
117 views

Reference an entire sheet in Google Sheets [duplicate]

Is there a way to reference an entire spreadsheet with a cell reference in Google Sheets? Say I wanted to find out how many cells in Sheet1 were non empty. In sheet two I'd want to do something like ...
2
votes
3answers
2k views

Add a time picker to Google Docs Spreadsheets?

Is there an option to add a time picker on Google Docs Spreadsheets? I don't need timezones or anything complicated. Just a string on the HH:MM format, that matches the custom format available. I don'...
2
votes
0answers
273 views

How to change default row height of a migrated Google Spreadsheet

The old Google Spreadsheet has a default row height of 17. The new Google Sheets has a default row height of 21. After a spreadsheet is auto-migrated by Google to the new Google Sheets, the original ...
2
votes
0answers
175 views

How do I create a map from Google Sheet data that's in another sheet?

I'm trying to create a Google My Map based on addresses in a Google Sheet "file". This seems to work OK following the standard instructions, except that I can't seem to extract data on anything except ...
2
votes
0answers
361 views

Batch Hiding Function in Google Script

I wrote a function that iterates through every non-blank row in the sheet, checks the value of a certain cell, and then if that cell matches my criteria its row number is added to an array. I then ...
2
votes
0answers
303 views

Google sheets equivalent of self-referencing in Excel

This is, in my opinion, one big advantage Excel has over Google sheets is the self-referencing formulas. What I mean by that is: say in cell A1, I'm changing the value in there a,b,c. And according ...
2
votes
0answers
119 views

Google Sheets Cells don't calculate when printed or viewing as PDF

Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas. Payroll tab is a reflection of our master database so i'm not dead ...
2
votes
0answers
184 views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
2
votes
0answers
80 views

Can't unmerge cells

I have a lot of data in a Google spreadsheet that depends on charts I spent ages trying to make look pretty. There is a stupid block of merged cells that won't unmerge, and when I try to cut-and-...
2
votes
1answer
804 views

Changing cell color in a range

i have 4 columns of values in each cell, the cells are different colors : Cell A1 = 12 (color - Green) Cell B1 = 10 (color - Pink) Cell C1 = 11 (color - Blue) Cell D1 = 13 (color - Red) Cell ...
2
votes
1answer
72 views

Change logs for Google Spreadsheets

We've got a large shared spreadsheet which is a chart showing user permissions. It's writable by several people. Although Google Drive allows you to roll back the changes simply its interface isn't ...
2
votes
0answers
766 views

Apps Script Webapp accessing Google Spreadsheet gets #VALUE! on every cell

I have a Google Spreadsheet with a couple of sheets that get data from other spreadsheets via ImportRange function and then calculate numeric values based on that data. Then, I assembled a code ...
2
votes
0answers
96 views

Automatic data sharing between Google Docs files [duplicate]

Is it possible to automatically update information on a Google Presentation or Document based on information in a Google spreadsheet? For example, I could create a presentation that contains a graph ...
2
votes
0answers
1k views

Sorting on a web-published Google Spreadsheet

I have a Google Spreadsheet that is published to the web and embedded in a website. This spreadsheet has data about hotel rooms, such as hotel name, number of rooms, date in and out, etc. I would ...
2
votes
0answers
491 views

Google Spreadsheet - Timetable? [closed]

I'm trying to use Google Spreeadsheets as a time report tool where I insert the number of hours worked on a project, the cost per hour & the total cost for this post. My sheet looks like this: ...
2
votes
1answer
11k views

Google Spreadsheet column limit [closed]

I'm working on a Google Spreadsheet and I keep getting this message: This spreadsheet is about to reach the following limits: Number of Columns Google Docs Help says that the columns limit is of ...
2
votes
1answer
1k views

Daily calculation and record insertion in Google Spreadsheet

I have a Google Spreadsheet that I am using to checkout last prices or levels of certain stock indices's. I have two worksheets in the workbook, one contains the indices's and a calculation I am ...
2
votes
1answer
1k views

Google Apps Script - Want to email specific spreadsheet row data on event

Scenario Basic support ticket clone: Users submit a form (e.g. answering fault reporting questions) Data is obviously stored as rows in the corresponding spreadsheet I've added an onEvent trigger ...
2
votes
3answers
2k views

Calculated range based upon some criteria

Given this layout: +--------+ +--------+ | A | B | | A | B | +----+---+ +----+---+ | 1 | | | 1 | 6 | | 2 | | | 2 | | | 3 | | => | 3 | | | | | ...
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 ...
2
votes
2answers
173 views

How to do sum(max(…))?

Here is my raw data: Site IP Visits Age (in hours) 1 uu.com 1.2.3.4 10 3 2 vv.com 2.9.7.5 2 4 3 kk.com 2.9.7.5 8 24 4 uu.com 1.2....
2
votes
1answer
868 views

Copy one Google Spreadsheet to another

I am developing a Google spreadsheet that I want to make publicly accessible to a community. I've released the first version of it, and now everybody in the community has that first version's link ...
2
votes
2answers
800 views

Is there a way to add a row to a Google Spreadsheet via a script?

I'd like to be able to call a script, which will add a row to a Google Spreadsheet, and manage stuff like user login. For example, gdocs_addrow --id <doc-id> name="John Doe" age="31" I know ...
2
votes
1answer
988 views

Problem adding Image to hyperlink in Google Sheets

I want an image to be my hyperlink in Google sheets. Here is my formula: =HYPERLINK("google.com/de", IMAGE("http://s5.favim.com/orig/69/hi-love-quote-start-Favim.com-658946.jpg")) The image shows ...
2
votes
4answers
3k views

Can I make a sheet private in a Google Drive spreadsheet?

I have a Google Drive spreadsheet with multiple sheets. The first sheet pulls data from all the other sheets. I need to share the first sheet with all users. But I only want to share the rest of the ...
2
votes
2answers
50 views

Extraction of integer value from dropdown list inside the same cell?

Using Google Sheets. In one cell, I have a dropdown list that is generated from a concatenation of integer ID column and name column. To get the value of the integer from the selection, I have another ...
1
vote
3answers
651 views

In Google Spreadsheets, how can I convert a number according to a range?

I'm working with Google Apps (spreadsheet) and I need to convert results (ranging from 0 to 36) to a maximum of 7 according to the following table. if score is (0-5) result should be 1 if score ...
1
vote
3answers
710 views

Sum of two numbers with a minimum of zero

I have a Google Spreadsheet with two columns of numbers (A & B). I want to add these numbers together and put the result into a third column (Results). If the sum of these two numbers is less ...
1
vote
2answers
40 views

Google Sheets formula for “if contains”

I'm trying to figure out how to identify 'if' a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains "sites, sheets, docs, slides". I want cell B1 to display a 1 'if' ...
1
vote
2answers
2k views

Basic date manipulation in Google script

I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read ...
1
vote
2answers
2k views

Counting unique rows in spreadsheets

How do you run the COUNTUNIQUE function on several columns? Running COUNTUNIQUE(A1:C3) gives me 8, because there are 8 unique values, I want it to return 3 because I have 3 unique rows. | A | B | ...
1
vote
3answers
60 views

How to display ZIP codes in Google Sheets?

Boston has leading zeros for its ZIP codes. Google Sheets is very helpful in removing leading zeros but unfortunately it removes them from Boston ZIP codes. I tried to find a number format for ...
1
vote
2answers
163 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
2answers
984 views

Categorize cells in Google Spreadsheets

I'm using Google Spreadsheets to manage my shared expenses with my girlfriend. It looks like this: | Who | What | Category | When | Amount | | Gui13 | Vegetables | Meals | ...
1
vote
1answer
2k views

How to split, trim, and sum up numbers in spreadsheet text cells?

I have a series of cells in a Google Spreadsheet containing string such as: A1 = "X5.1 Y4.3 Z2.8" B1 = "X2.5 Y1.9 Z4.8" C1 = "X6.2 Y1.9 Z3.4" I need to get the sum of all the numbers that appear ...