EzDevInfo.com

How do you add UI inside cells in a google spreadsheet using app script?

I'd like to add buttons to specific cells in Google docs spreadsheet. The apps script UI documentation talks about how to add a new panel, but it's not clear how UI in that panel could be attached to specific rows or cells.

Is it possible to add UI to particular cells, or are we limited to adding new panels?

Check cell for a specific letter or set of letters

In a Google Spreadsheet, I want to use a formula that will output a certain text or number if a certain cell contains certain letters.

For example, if I put =IF(A1="Black","YES","NO") and A1 is equal to "Black" - it outputs "YES" correctly. However when I put =IF(A1="Bla","YES","NO") it outputs "NO". Is there any formula that I can use that I can say something like =IF(A1 CONTAINS "Bla","YES","NO") so it outputs "YES".

Please advise.

Access Google Spreadsheet or Google Data API only with Javascript

I want to access Google Spreadsheets using JavaScript Only. ( no .NET C#, Java )

I came here and was shock to know that there is NO api for JavaScript for accessing Google Spreadsheet.

Please tell me how to access(CREATE/EDIT/DELETE) Google Spreadsheets using JavaScript or any of its frameworks like jQuery,etc or so.

Counting number of occurrences in column?

What would be a good approach to calculate the number of occurrences in a spreadsheet column? Can this be done with a single array formula?

Example (column A is input, columns B and C are to be auto-generated):

|   A   |   B   |   C   |
+-------+-------+-------+
| Name  | Name  | Count |
+-------+-------+-------+
| Joe   | Joe   |     2 |
| Lisa  | Lisa  |     3 |
| Jenny | Jenny |     2 |
| Lisa  |       |       |
| Lisa  |       |       |
| Joe   |       |       |
| Jenny |       |       |

Refresh data retrieved by a custom function in google spreadsheet

I've written a custom google-apps-script that will receive an id and fetch information from a web service (a price). I use this script in a spreadsheet, and it works just fine.

My problem is that these prices change, and my spreadsheet doesn't get updated. How can I force it to re-run the script and update the cells (without manually going over each cell)?

Google Spreadsheet Conditional Formatting Based on another Cell Value

I'm using Google spreadsheet and working on a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green but if it's below, it will be amber/red.

Is this available in google spreadsheet functions or do I need to insert a script. Thanks a lot

"MMMM yy"-date in google spreadsheet

I have a google spreadsheet in which I want a date with only the name of the month and the year, like September 2011, and I also want the month and year to be easily changeable. Is there any way of getting custom date formats to do this?

I figured out I could do like this:

=TEXT(40295; "MMMM yy")

But then the datepicker can't be used anymore and changing the date is made impossibly hard.. Is there any good way of solving this?

Google Spreadsheet: Script to Change Row Color when a cell changes text;

I have a googlespreadsheet where I keep a list of bugs and whenever I fix a bug I change the status from "Not Started" to "Complete". I want to write a script for the Google Docs spreadsheet such that whenever I change the status to "Complete" the entire row gets highlighted in a certain color.

I already know that Google Spreadsheet already has "change color on text" but that function only changes the color of the cell and does not change the color of the entire row.

How to create custom keyboard shortcuts for google app script functions?

I'm trying to get a sense of the viability of replacing some of my Microsoft Excel spreadsheets with Google Doc Spreadsheets. How can I create a custom keyboard shortcut to a google app script function in a google docs spreadsheet? This is something I commonly do with VBA and Excel.

On Google Spreadsheet how can you query 'GoogleFinance' for a past exchange rate?

I'd like to know if it is possible to query a past exchange rate on Google Spreadsheet.

For example; using formula =GoogleFinance("CURRENCY:USDEUR") will return the USD/EUR rate at this present moment. How can you retrieve a historic rate?

Google Spreadsheet, Count IF contains a string

I have a column like this:

What devices will you be using?

iPad
Kindle & iPad
No Tablet
iPad
iPad & Windows

How do I count the amount of people that said iPad?

This formula does work for exact matches but not if it contains an additional value:

=(COUNTIF(A2:A51,"=iPad")/COUNTA(A2:A51))*1

Any Suggestions?

How do I avoid the "#DIV/0!" error in Google docs spreadsheet?

I have a column with average(K23:M23) that starts out with #DIV/0! when the K23 through M23 cells are empty. Preferably I'd like to only do the average of cells that contain non-zero, non-blank values. I think it's possible using the query command:

https://docs.google.com/support/bin/answer.py?hl=en&answer=159999

But their example doesn't help me.

Selecting the last value of a column

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.

Something like:

=LAST(G2:G9999)

except that LAST isn't a function.

How to debug Google Apps Script (aka where does Logger.log log to?)

In google docs spreadsheets, you can add some scripting functionality. I'm adding something for the onEdit event, but I can't tell if it's working. As far as I can tell, you can't debug a live event from google docs, so you have to do it from the debugger, which is pointless since the event argument passed to my onEdit() function will always be undefined if I run it from the Script Editor.

So, I was trying to use the Logger.log method to log some data whenever the onEdit function gets called, but this too seems like it only works when run from the Script Editor. When I run it from the Script Editor, I can view the logs by going to View->Logs...

I was hoping I'd be able to see the logs from when the event actually gets executed, but I can't figure it out.

How do I debug this stuff??!

New Google Spreadsheets publish limitation

I am testing the new Google Spreadsheets as there is a new feature I really need: the 200 sheets limit has been lifted (more info here: https://support.google.com/drive/answer/3541068).

However, I can't publish a spreadsheet to CSV like you can in the old version. I go to 'File>Publish to the web' and there is no more options to publish 'all sheets' or certain sheets and you can't specify cell ranges to publish to CSV etc.

This limitation is not mentioned in the published 'Unsupported Features' documentation found at: https://support.google.com/drive/answer/3543688

Is there some other way this gets enabled or has it in fact been left out of the new version?

My use case is: we retrieve Bigquery results into the spreadsheets, we publish the sheets as a CSV automatically using the "publish automatically on update" feature which then produces the CSV URL which gets placed into charting tools that read the CSV URL to generate the visuals.

Does anyone know how to do this?