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

learn more… | top users | synonyms (2)

2
votes
2answers
199 views

Google Sheets ARRAYFORMULA not copying form responses

So I have a form setup which sends data into a sheet labeled 'Form Responses'. I have another sheet that mimics the column layout of 'Form Responses' but has formatting applied to it, (called ...
0
votes
0answers
28 views

Moving data between columns in google sheets

I want to manage my tasks in Google Sheets. Right now I list all my tasks in column C (as shown below) Is there a way to 'indicate' that a field in column C has been done (like I have in Column A) - ...
1
vote
1answer
103 views

Google spreadsheet - QUERY changes time format

I have this column with amounts of time (format set to number > duration), if I made a query of that column I get it with a different time format and I'm not able to set the proper format back again. ...
1
vote
1answer
286 views

Google sheets VLOOKUP issues not finding a value thats is in its range

Heres a link to a copy of my spreadsheet I am attempting to do the same formula using a VLOOKUP in 4 sheets to do a calculation called LSI. In the first sheet I have the raw data from a form coming ...
-1
votes
0answers
32 views

Array in Google Spreadsheet that tracks totals per name [closed]

I am building a spreadsheet that tracks "stats" for a group of people that change from week to week but also have people participating in the sport on multiple weeks. In my array I have a column ...
1
vote
1answer
37 views

Google Sheets list key with highest value in key value pair

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
1
vote
1answer
131 views

IMPORTXML: xpath to get non-distinct parent node data for each child node

Google Spreadsheets has an IMPORTXML function, which I am trying to use to maintain a list of TV episodes, based on the API at http://services.tvrage.com/feeds/episode_list.php?sid=3183. I am trying ...
0
votes
0answers
40 views

“Service using too much computer time for one day” Can I see computer time per sheet?

With my Google Spreadsheets I keep getting "Service using too much computer time for one day" messages. I have already tried removing some older sheets and reducing calls to custom functions and ...
2
votes
1answer
38 views

Had a google spreadsheet query that worked but now doesn't

I made a copy of the spreadsheet that I'm working on here. The query that I have on on the sheets "DSM", "DiveTank", "WarmUp", "Teach" all have pretty much the same query. That worked before but now ...
3
votes
1answer
994 views

How to force a page break in Google Spreadsheets

I want to force a page break after a certain row of a sheet. Is there some analogue of Google Docs’ (and Excel’s) “insert page break” for Spreadsheets? If there is no “native” way to do this, maybe ...
0
votes
1answer
153 views

Google spreadsheet query reference to several sheets

I have this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),"select* offset ...
1
vote
1answer
49 views

Associate cells to imported ones with importrange in Google Sheets

I have the following issue: I have table A with several columns and I use the following function to import some of data to table B: ...
1
vote
0answers
28 views

Why is there sometimes no “Move to trash”/“Move to bin”

I'm trying to clean up some old Google Sheets, but some of them don't have a "move to bin" menu option: Other spreadsheets do have it: What's the difference? How can I delete spreadsheets that ...
0
votes
1answer
93 views

Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically

I made a spreadsheet for tracking weight, where each row is one day, and one column is weight. Although I've had Google Spreadsheet create a row for every date in the year, I only measure my weight a ...
1
vote
1answer
95 views

Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
1
vote
1answer
86 views

Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK($F3)>0, COUNTBLANK($G3)>0, COUNTBLANK($H3)>0, COUNTBLANK($I3)>0), "Missing Values", $E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, ...
0
votes
1answer
32 views

Tolerant Sum of column

I would like to make the sum of a column which might also contain cells which are not parseable as a number (strings, empty, etc). These cells should just be ignored. I've tried: ...
1
vote
1answer
48 views

How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to ...
2
votes
1answer
313 views

Google Sheets Query Get Row When Any of These Cells are Not Empty

So like my title states I would like to select via a Query only the rows where certain cells are not blank/empty. I have 4 pools that I have a form for collecting their chemistry. In separate sheets ...
2
votes
1answer
33 views

Convert table data into a list in Google Spreadsheet

I'm using Google Spreadsheet and would like to convert a table into a single column of values so I can paste them into another program. Original Data: x y z a b c Desired Outcome: x y z a b c ...
1
vote
1answer
344 views

How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
3
votes
1answer
186 views

Created a Google Form; can't find the Spreadsheet

Long ago, I created a survey that I sent out by email. The responses were recorded in a Google Spreadsheet by filling out a Google Form. I would like to use that data again, but I cannot find the ...
1
vote
1answer
31 views

Using a cell text as sheet reference

Is it possible to use a cell text as a sheet reference in Google sheets. I have a spreadsheet with many sheets and want to reference a sheet name from text in a cell rather than having to put the ...
4
votes
1answer
139 views

How do I copy cell values programatically?

How can I do this programmatically in Google Sheets? How can I copy cell values rather than references from one sheet to another in Google Docs? I have a RSS feed, that I am working along side, so ...
0
votes
1answer
94 views

Find the nth occurrence of variable, look at adjacent cell and organise in an array

I have a Google spreadsheet with two arrays: The first is a list of ~100 names (in a single column) with the adjacent column being the variable (A,B,C,D,E,F,G,H,I or J). The purpose is to group people ...
1
vote
1answer
38 views

How to grant a specific list of user to access Google Form?

Currently when I click on the "Send Form" button in Google Form and user will be able to input the form when they know the URL. Is it possible to grant a specific user to have access to the form? (So ...
2
votes
1answer
214 views

How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an Incidents sheet: | Date | Name | Incident | |------------|-------|---------------------| | 01.01.2015 | Vidar | Car broke down | | ...
2
votes
1answer
100 views

Mailmerge via Google Sheets: multiline cell content

I'm trying to create a batch of email messages using data from a Google Sheets. The Sheets has these columns: name, email address, message I tried 2 google Add-ons: Yet Another Mail Merge (uses ...
0
votes
1answer
45 views

Eliminate empty cells from a sort

I have a spreadsheet where club members scores are added from competitions during the year. I accumulate the data to a sheet with a name column and a value column I then sort these to another sheet by ...
2
votes
0answers
110 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
1answer
60 views

Cell concatenation by hotkeys rather than a formula

In my fumble-fingered attempt to copy (supposedly with Ctrl+c) part of a column containing a few text strings in individual cells within new Google Sheets, on pasting (supposedly withCtrl+v) I ended ...
1
vote
0answers
48 views

Dynamically creating sums from lookups

I was trying to do something similar to this post: Google Sheets advanced lookup + sum But I just can't figure out how to amend it for my purpose. I'm basically building a diet plan, where I have ...
3
votes
1answer
156 views

Select top n filtered rows in Google Sheets

In Google Sheets I have a table like this: JOB TYPE STATUS Generate files Scheduled In Progress Update certs Manual Completed Copy files Scheduled Pending ... ...
2
votes
2answers
86 views

Conveniently export part of Google spreadsheet to HTML page

Google spreadsheet allows you to save table as HTML: The problem is that what the HTML you get is not publishable in website or coherent HTML document: I'd just like a part of the table as HTML. ...
4
votes
1answer
114 views

Explaining proper meaning of columns to Google charts editor

I'm trying to make a simple chart displaying number of events that occured each month in X years. This means I want a chart that shows: X colored lines, one for every year Month names marked on the ...
4
votes
1answer
53 views

Save daily to my Gmail in Google Sheets

Right now what I have is this in a Google sheet for my company daily lunch program: function sendLunchPerkSummery() { var d = Date(); var file = ...
1
vote
1answer
322 views

How to pass multidimensional array to a custom function in Spreadsheets

I want to pass a multidimensional array to a custom function. I do it like this: =customFunction({{"a1v1"; "a1v2"};{"a2v1"; "a2v2"; "a2v3"};{"a3v1"}}) However I don't get the expected behaviour ...
0
votes
0answers
63 views

Trying to convert “=importhtml” to UrlFetchApp.js

I'm trying to replace my cell which is =importhtml into a .js for Google App Script. The current function in Google Sheets now is =importhtml("http://www.gurufocus.com/financials/"&'Raw ...
0
votes
0answers
265 views

How can I stop Google Spreadsheet Now() function from auto updating?

I'm using the now() function as a shortcut to insert the current timestamp. However, the value of the time in the cell changes whenever the spreadsheet is modified. How can I stop the value in the ...
1
vote
0answers
198 views

How to save temporary variables in Google Spreadsheet formula?

I'm trying to create a formula in Google Spreadsheet which looks like this: if (x < 0, x + 1, x) It means if x is smaller than 0, return x + 1, else return x. However, x itself is an ...
0
votes
0answers
90 views

Google Spreadsheet COUNTIF in ARRAYFORMULA

I currently have this formula in column P, starting in P2 and continuing down the page, =IF(A2="", "", COUNTIF(E$2:E2,"Rejected (overdue application)")) =IF(A3="", "", COUNTIF(E$3:E3,"Rejected ...
2
votes
1answer
30 views

Select range by typing it in, e.g.,“f1:f600”

How do I select ranges by typing in the range value in Google Spreadsheets? I need to be able to select, say, d3:d1000, by typing in “d3:d1000” somewhere. In other words, what F5 does in Excel. I ...
0
votes
0answers
37 views

Fill-down in Google Spreadsheets

I'm looking for something in Google Spreadsheets that provides fill-down functionality. Equivalent to Ctrl-D in Excel. I am selecting a range of cells for the fill using named ranges. The range is ...
0
votes
0answers
27 views

How do I improve this encodeURL Google Sheet custom function so that it no longer invokes too many times per second?

Here is my Google Sheet function encodeURL: function encodeURL ( r ) { if ( r.constructor == Array ) { var out = r.slice(); for ( i=0; i< r.length; i++){ for ( j=0; j< ...
1
vote
0answers
81 views

onEdit Script to auto run Filter on a tab

I'm trying to find a script that will run in a particular way, see below: it runs on a specific tab in a spreadsheet needs to either re-run a filter that i have in place on the tab OR can filter out ...
1
vote
1answer
91 views

How to put a dollar symbol before every column and row number in google spreadsheet

I have a big spreadsheet in google docs with many formulas like this: =MIN(C3;C4)/500+4. Now we need to add $ before every cell row/column to make it absolute. So, the formula should look like this: ...
1
vote
1answer
139 views

Google Docs invitation to edit on my Outlook email address

I have got an invitation to edit a Google doc on my outlook.com email address, but every time I click on the link, I get the message “You need permission”. I am signed in to my Gmail account as well ...
2
votes
0answers
271 views

How to set Custom data validation in Google Sheets (A really simple step-by-step guide needed)

I'm trying to figure out how to create custom data validation in Google Sheets what are the steps I need to follow to achieve this? For example if I wanted the following validation: // Set the data ...
1
vote
1answer
91 views

Pre-fill field with formula — incremental counter — in Google form [duplicate]

I have a Google Docs form that I use to track my customers complaints. Is it possible to have a "counter" field pre-fill with an incremental number? I want my customers to have a complaints number ...
1
vote
1answer
46 views

Function to retrieve a subset of rows coming from another function in Google Sheets

I have a function in a Google Sheet that returns a handful of columns and two rows. One of those rows contains column headings, which I would like to remove. Is there any way to wrap this function ...