One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
0answers
9 views

Auto Sort data in Google sheet, but by sheet not range

I have found this script from this question to auto sort a column: How can I apply a script to only one specific sheet within a spreadsheet function onEdit(){ var sh0 = ...
3
votes
1answer
27 views

How can I transpose multiple rows into a column with multiple matched outcomes?

I have this data set: and I want to transpose it where the Date is in a Column then A is in the next Column and B on the next I used this formula = ...
0
votes
0answers
9 views

Clipping text (with newlines) vertically in google spreadsheets

I would like to have each row to have a hight of one row of text but I would like cells to contain text with newlines (i.e. adresses) while still retaining that minimal height. Is that even possible?
2
votes
1answer
19 views

Google Spreadsheet Timestamp on Edit?

I got the major part of answer on this link: Google Spreadsheet Timestamp? The code on this link is below: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Sheet1" ...
-2
votes
0answers
8 views

Make a copy of the current row and insert it on a new row directly underneath it in Google Sheets [on hold]

I'm using a Google Sheet to keep a list of visitor's names, number of visits, and the date they come. I would like a "macro" in Google Script, to copy the current row, insert a blank row under the ...
4
votes
1answer
22 views

Multi-column Conditional Formatting and Row Export in Google Sheets

I need to (a) indicate and (b) export several columns of data into a separate sheet, but only the rows that satisfy a certain condition: three columns (which are not exported) must be after today's ...
2
votes
1answer
31 views

Importing columns across two Google Sheets in same spreadsheet

I have one spreadsheet which houses the database of my members. In the same spreadsheet, I have another sheet where I will track attendance for the events for each of these members. First issue: ...
1
vote
1answer
19 views

Method to filter based on dropdown list of months

I'm trying to convert an Excel spreadsheet into Google Sheets. I have the entire year on a sheet and would like to filter the list based on a selected month from a dropdown. For example, selecting ...
0
votes
1answer
20 views

Multiply cell value depending on keyword

I would like to apply a transformation to a table which basically looks like this: If there is a certain keyword in column A, then the number in column B should be multiplied by 2. Is that possible ...
2
votes
1answer
19 views

How to split then combine data to rows

I have a Google Spreadsheet that receives data from a Quickbooks invoice entry. The data from item description has the quantity of each item separated by commas, then the items which are also ...
2
votes
1answer
20 views

Trigger Script in Spreadsheet in Similar Fashion to a Link

I currently have a button on my spreadsheet (a text box image that triggers the script). Unfortunately this button doesn't work when using a tablet. Is there a way of triggering a script to run via a ...
2
votes
0answers
30 views

Adding columns automatically to Google Sheets

I am using Zaption and Socrative as assessment tools while teaching. After I download the CSV files, I import them to Google Sheets. I figured out a way of having the results sent to a PDF after ...
3
votes
1answer
14 views

Creating a copy of a sheet by copying values only?

I use the below script to basically duplicate an existing worksheet. The worksheet I am copying contains the ImportRange. I don't want the copy spreadsheet to have the formula just the value from the ...
1
vote
1answer
14 views

Using IF Formula to get data from another Cell

I am just trying to use the IF formula: COLUMN A COLUMN B COLUMN C Cats NULL Dogs Monkeys Kittens NULL ...
4
votes
1answer
62 views

Custom script “invoked too many times” error

Credit: Google Spreadsheets function to find distance between cities function getDirection(city1, city2, unit) { var directions = Maps.newDirectionFinder() ...
-1
votes
1answer
12 views

How do I block rows/columns/cells from certain users on the shared Google Spreadsheet?

I have a spreadsheet with sensitive data. How might I block a certain column from others users viewing it?
1
vote
1answer
20 views

Script to Create Copy (Backup) of entire spreadsheet onOpen

Is it possible to write a script or is there another method of backing up a spreadsheet when opened? The reason is: I have other users opening my spreadsheets and if they completely stuff everything ...
1
vote
1answer
22 views

How to change the size of a published Google Spreadsheet chart that has its own sheet?

Google Spreadsheet offers the option to put a chart on its own sheet (instead of inserting it in the current sheet). This looks like a good idea for large charts. However, I ran into an issue when ...
1
vote
1answer
17 views

Calculate compound interest rate in Google Spreadsheet

Assume the PV=100, FV=150, yearly IR=X, number of period = 60 (5 years of interest and compounding is monthly basis) Therefore, FV=PV*(1+X/12)^60 How can I calculate X easily? Technically I can ...
2
votes
1answer
47 views

Highlight cells, which content can not be parsed as number

How can I highlight cells whose content is not parsable as number? thanks!
1
vote
1answer
54 views

Query(ImportRange) throws “Formula Parse Error”

I'm working with Google docs and I've run into a wall with a query(importrange) function. =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select ...
2
votes
1answer
37 views

Script to create a new worksheet in Google Spreadsheet

I want to be able to have a button on a Google Spreadsheet. The user clicks on the button and a new worksheet is created based on a template (or blank existing worksheet). The new worksheet should ...
4
votes
2answers
28 views

How to sort a Google Sheets Query by a custom ordering

I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)? ...
2
votes
1answer
18 views

How to order category by numerical order with text in cell

I want to arrange my C column by numerical order, however the cells themself have text in something like "503 in Kitchen & Home", I want it to detect the number but ignore the string. Is ...
3
votes
1answer
20 views

Freeze columns in Google Sheets for multiple users

Is there any way for multiple users working on a Google Docs spreadsheet to freeze columns and rows the same way filter view works?
1
vote
1answer
33 views

If time is before 17.00 use 17.00 OR if time is after 17.00 use value in cell

Headline says it all. I'm trying to calculate worktime in minutes. Ex: https://docs.google.com/spreadsheets/d/1GmGyi4kQf8I2qvEJjfG3OZvm_n_kBbsw_doTi9J0KCk/edit?usp=sharing I have to state when ...
3
votes
1answer
23 views

Extending dget to a column in Google Spreadsheet

Given the following formula: =dget(Sheet2!$A:$C,Sheet2!B$1,{"USER ID";$A2}) Is there a way to alter this formula so that it fills all the values in a column? I've tried with arrayformula, changing ...
4
votes
1answer
35 views

Formula for smart, scale-aware number-formatting in Google Sheets? (e.g. 1,024 → 1k, 1,816,724 → 1.8M)

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
1
vote
1answer
17 views

Trouble linking entries from two sheets for automatic update

How do I have text information from one column (Sheet 1, column C) automatically feed into Sheet 2 column A so that, when Sheet one is updated through the Google form it is linked to, the information ...
1
vote
1answer
33 views

How can I trace precedents in Google Sheet

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets? I have got the code for dependents from http://webapps.stackexchange.com/a/50149/88163. ...
3
votes
1answer
23 views

Link to a filtered view based on params

I have a spreadsheet1 with the following columns: name, country, value In another spreadsheet2 I'd like to have set of clickable links based on parameters: param1, param2, link where: link ...
2
votes
1answer
34 views

Subtract values in a list that fit certain criteria within Google Sheets

I've got this sheet where I do some basic accounting, and I want to be able to input the balance in my pocket every once in a while. The idea is that the expenses I record until the next balance will ...
6
votes
2answers
105 views

How to automatically increase the exponent in Google Spreadsheet?

In a Google Spreadsheet, I put in the following numbers in a column, each line is an entry in the column. 100 100*1.01 100*1.01^2 100*1.01^3 The highest exponent of 1.01 is 3 for now. But I would ...
1
vote
1answer
18 views

Google Spreadsheets Dropdown Lists

So I'm using Spreadsheets to tabulate some data. I have 2 sheets, one that displays everything with drop down lists, and the second that has the information. The second sheet looks like this: ...
4
votes
2answers
37 views

AutoFill dates but skip weekends

I need to auto fill the some dates which is easy and I know how to do. However, I do not know how to do this skipping the weekends. I want it to auto fill skipping weekends and jumping to Monday.
1
vote
1answer
29 views

I need to hide/show a group of columns in Google Sheets in a simple way

I need to hide and show in a easy way a group of columns in Google Sheets. In Excel I used a Macro with a button, but in Sheets I cannot put a button. How can I add buttons with custom functions in ...
1
vote
1answer
20 views

How do I find what is in a particular cell on the same row as another on based on a match?

I have the following query in Google Sheets: =IF(ISERROR(MATCH(A2,Sheet1!A:A, 0)), "No Match", "Match on Row " & MATCH(A2,Sheet1!A:A, 0)) I want to return the value in column C of the matched ...
1
vote
1answer
15 views

Custom highlighting style on Google Spreadsheet

I am new to Google spreadsheet and I'm wondering if there are any other ways to draw attention to a box rather than just change its colour. I want to emphasize a box drawing a circle on it. I saw ...
0
votes
0answers
30 views

Google Spreadsheet, Google script, calculate

I want to grade my students and give them feedback. So they make an excercise, I will give them the feedback if they do it perfect or not perfect. I want also give them grades. Question: So I have ...
2
votes
1answer
10 views

Google Sheets Problem with Chart Axis

I have a Google Sheet with a some data that I need to chart. The horizontal axis should be a list of dates. When I plot the chart, though, it's inserting dates that aren't part of the data set. For ...
3
votes
1answer
34 views

Automatic currency conversion in a Google spreadsheet

I would like to add an automatic conversion thing to my spreadsheet. I have two columns, A1 titled Money in USD and B1 titled Money in BRL. I want to make it so whenever I enter a value in the USD ...
2
votes
1answer
13 views

Need to be able to refer to a sheet within a count function

I have a Google Spreadsheet I'm working on. Each tab has new data that's pasted in on a weekly basis, for example, I just created a new sheet called 5/5/2015 and included the data within. What I'd ...
1
vote
1answer
27 views

How can I get a unique ID when I submitted an answer in Google Form?

I am using Google Form for my customers to submit their opinion. I read this and this, but I have some questions which these two posts haven't mentioned. Is it possible for my customers to know their ...
3
votes
1answer
23 views

How to count numbers in nearby cell with criteria in Google Sheets

I am trying to use Google Sheets to count the hours that my staff works every month. So far I have this: In the left, my staff write their name and the account of hours they have worked that day. ...
2
votes
1answer
25 views

Conditional Filters Formula in Google Sheets

So I have a cell C4 in which I have an item list for a data validation. Below, I want to create a filter that will take all the entries in another sheet named All and compare that to the C4 cell. So ...
3
votes
2answers
38 views

Joining Two Tables and Merging Cells

Say I have list of users and their info. Each user can have multiple accounts, and the "paid" status of each of those accounts may be "no" or "yes". A user may have one or two paid accounts. My first ...
1
vote
0answers
17 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 ...
3
votes
1answer
38 views

Can I use Google Chart Gallery in Google Spreadsheet?

Google Spreadsheet has column charts but I want to use "drawAnnotations" like this (Annotations). Column chart does't show bar value as "drawAnnotations" on top. My chart included these; First ...
1
vote
1answer
16 views

Why does not the picture in column A move with the sorting A to Z of column B?

I'm using Google Spreadsheets, first column is picture then B column is name. When I want to sort it A to Z, all the names get in alphabetical order, but the picture stays in the same position.
2
votes
1answer
28 views

How to sort Google Sheets columns by row height

I'm working on a sheet that involves multiple columns corresponding to various data. Each column has a varying number of cells of varying heights with wrapped text. What I want to do is make the sheet ...