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

learn more… | top users | synonyms (2)

2
votes
3answers
2k views

Google Docs - a formula that does Excel “countifs” function involving dates

I have a Google spreadsheet in which multiple users are tracking a list of dates and class names (math, reading, writing, etc). On a separate sheet in the same document, I want to be able to give a ...
2
votes
1answer
2k views

How to escape a double-quote in a TSV file for import into a GoogleDocs spreadsheet?

How does one specify a cell to contain exactly one double-quote character in a TSV file so that it will be handled correctly when imported (as a spreadsheet) into GoogleDocs? (Note: when importing ...
2
votes
1answer
8k views

Filter rows in Google spreadsheets

I have a Google spreadsheet. There is a colour column. Every row has a colour, Red, Yellow, Green etc. I want to be able to filter out say all rows that are Red, all rows that are yellow, all rows ...
2
votes
1answer
5k views

Apply Formula to Newly Inserted Row

I have a spreadsheet that keeps a running balance of a credit card with the newest transactions at the top (just like credit card web sites). An example of the formula for each cell in the Balance ...
2
votes
1answer
1k views

Batch copying/renaming in Google Docs

I need to copy and rename a base form (a spreadsheet) to several other forms in Google Docs. Imagine I have the following form: My Base Form - @name (@course) After running the script or whatever, ...
2
votes
2answers
2k views

Google Spreadsheet: Auto-Update via RSS (and keep history)

I have a google spreadsheet that fetches some data via RSS. The RSS feed is updated once a day, everyday. Is it possible for me to keep archive of all the new & historic data in the google ...
2
votes
2answers
3k views

Is there a way to upload a .csv file to Google Docs using a Google spreadsheet as a template?

I am generating a .CSV file with an application. I then upload the .CSV file to Google Docs, which creates a Google Docs Spreadsheet which is "plain and white". I then have to manually format the ...
2
votes
1answer
1k views

Google Spreadsheets Enter/Return Key Behavior

In Microsoft Excel, the default behavior of pressing the Enter (return) key is this: If you are in a cell, and tab over as you input data, at the end of the series when you click Enter, the ...
2
votes
1answer
55 views

Print several non-consecutive columns of a Google Spreadsheet using a script

I have script for print. I need print range without some column (e.g. print just B,C,D,E,G and miss F) and without all notes in sheet. The script below prints all the columns. function printPdf() { ...
2
votes
1answer
37 views

IMPORTHTML at a certain time of day in Google Spreadsheets

I am looking for a way to import the table from this formula once per day at a certain time instead of automatically updating as the formulas seems to do. I need to do this in order to track certain ...
2
votes
1answer
27 views

Variable GoogleFinance formula (Google Spreadsheets)

I have a question regarding a formula: =GoogleFinance("CURRENCY:(A2)EUR")*B2 The content of cell A2 = USD, the content of cell B2 = 60, so the formula should be translated as ...
2
votes
1answer
20 views

How would I apply formatting to another cell based on whether another cell's date has passed or not?

I'm working on making a spreadsheet in Google Sheets, and I'm attempting to have a column of names and then a column of dates adjacent to it. I'd like to set up some sort of conditional formatting ...
2
votes
1answer
39 views

Complex views in embedded Google Sheets

I have a Google Sheets contact list for a multicampus boarding school. The sheet at present summarizes the contact info for each person into the first 4 cells of the row. Entry is made using the ...
2
votes
1answer
36 views

How to automatically wrap text to new responses from form?

I had a working Google Spreadsheet where when new responses were filled in on the form, they would automatically get textwrapped on the Form Responses sheet. Now recently I had to remake this ...
2
votes
1answer
81 views

Import JSON to Google Sheet using Google Apps Script

I am using Apps Script in Google Sheets to import JSON from Google Analytics. I have previously used the ImportJSON function to easily get the JSON from an open API just using =ImportJSON(url) within ...
2
votes
1answer
36 views

Can this script be more optimized and why is it running slow sometimes?

I just started using Google App Script and wrote this script (mostly copy and paste) to check the status of my website and its pages. Sometimes, mostly after opening the spreadsheet, but even if it ...
2
votes
1answer
22 views

How to get sums by group, and output only those, where sum is not equal to 0?

In Google Sheets, I have the following data: | A | B 1 | foo | 20 2 | bar | 50 3 | baz | -40 5 | bar | -50 4 | foo | 100 6 | baz | 60 Currenly, I'm using this query ...
2
votes
1answer
73 views

Automatically copy and paste cells that have just been updated to a list from forms

I have a spreadsheet that collects information from two different forms. Each form feeds a different sheet in the spreadsheet. There are 3 other sheets that the info from either of the first two forms ...
2
votes
1answer
19 views

How to concatenate 1 in front of many cells at once on a google sheets doc?

I am trying to concatenate many cells of a google sheets with "1 " in front of every cell. There are 360 cells so changing each individually would be a difficult task. Any help would be greatly ...
2
votes
2answers
38 views

How to word count multiple rows in Google Sheets?

I have a spreadsheet similar to: A B C 1 "text a1" "text b1" "text c1" 2 "text a2" "text b2" "text c2" 3 "text a3" "text b3" "text c3" 4 "text a4" ...
2
votes
1answer
26 views

Cannot plot null values in Google Forms

I have a Google Form with questions that I collected the responses from. Now I am trying to create the charts. I click on Responses -> View responses, then I click on Explore to see the chart. The ...
2
votes
1answer
34 views

How can I round a Google Form input to nearest .5?

I'm trying to write a script that will calculate based on user input from a Google Form and then send a customized email based on the input provided. I've written an apps script bound to the ...
2
votes
1answer
105 views

Google Spreadsheet Scatter Chart with Multiple Data Series

I want to create a scatter chart with Google Spreadsheet which turns out to be very difficult. Based on a similar question here. I came up with this approach: What I want to achieve is sketched ...
2
votes
1answer
24 views

When creating a 'Checkboxes' question in Google Spreadsheets, what formula do I use to add a mark to each box ticket correct to the answer?

I am creating a Google Spreadsheet for the first time and I have suddenly become stuck. When filling out my spreadsheet I do not know what formula to use when adding up the correct answers. I have ...
2
votes
1answer
29 views

Using a script to process form input every time a form is submitted

I'm trying to have it that every time I submit a form, it adds automatically some data. This is the script I have: function onFormSubmit(Test) { var sheet = SpreadsheetApp.getActiveSheet(); ...
2
votes
1answer
16 views

Date automation down columns

So I know how to get google spreadsheets to automate dates down a column. Write a date, left click on the crosshair in the bottom right hand corner, drag down and let go. The thing I'm hoping to do ...
2
votes
1answer
34 views

Trying to query a range of names in Google Spreadsheets, query is failing with the space in the names

I'm trying to query a range of names in Google Spreadsheets. When I try and make a query like: =QUERY('QA Import'!A1:H, "select A where G = "&B2&"") where B2 is John Doe I get the error ...
2
votes
1answer
17 views

Google spreadsheet: Show 2 formulas or more in a cell

I just want to know if there's any way to show 2 or more formula values in the same cell. P.E. =ARRAYFORMULA(A1+A2) =ARRAYFORMULA(B1+B2) =ARRAYFORMULA(C1+C2) Reason: I want to make a ...
2
votes
2answers
40 views

Suppress time part of the datetime values on a chart axis

I have a line chart based on the following data, capturing the Close Queue size every four hours: +---+-------------------+-------------+ | | A | B | ...
2
votes
1answer
46 views

Rearranging Google form data in a new sheet

I am trying to use a form to create a list of jobs for our student employees, which exists as an editable Google sheet. The thought, being any of the staff could add jobs that need to be taken, by ...
2
votes
1answer
29 views

Conditional formatting based on portion of text

Is there a better way to specify conditional formatting based on another cell? For example, I have specified a Custom formula as the following (which works): =or($A:$A="bar foo", $A:$A="baz foo") ...
2
votes
1answer
16 views

Dragging functions through multiple cells with spaces in Google Spreadsheets

I have a function that needs to grab data from 1 row at a time on another worksheet. However on the worksheet the function is on, I have extra spaces and dragging the function down causes it to ...
2
votes
1answer
31 views

Referencing New Cells When Original Selected Cells are between unwanted cells

We have a Google sheet that we want a set number of rows and columns to be referenced into another sheet. However, those rows and columns are in the middle of the original sheet, and we want ...
2
votes
1answer
28 views

How can I make COUNTIF with the condition on the nearest column?

I'm using Google Spreadsheets to consume the data I got from survey. But I don't like the summary Google automatically constructed, I want to make more complex analytics. So, I have two rows, and my ...
2
votes
1answer
127 views

Can I use conditional formatting to color a column's cells' text based on the conditionally-formatted color of text in another column's cells?

I have been working on a spreadsheet in Google Sheets that serves as a database of the hundreds of characters who appear in a novel I am reading (to help me keep track of them all). Column A contains ...
2
votes
1answer
263 views

E-Mail from Google Spreadsheets: including image for signature takes out formatting of message

Following situation: Sending emails from google spreadsheets. I have a compiled text (with line breaks), which is stored in var message. I have been trying to include a picture for the signature. To ...
2
votes
1answer
48 views

“getAs()” error - send an Email with Attachment using GoogleAppScript [duplicate]

I am very new to GoogleAppsScript and struggling. Here is my problem: Trying to send multiple E-Mails via Spreadsheet. Want to attach a PDF to those mails. Getting the following error: TypeError: ...
2
votes
1answer
183 views

Google Scripts UI show new row as confirmation after form submit

I have tried several things, but decided to finally ask for help with this. I am using a Google Scripts UI form like the one found with the example to insert new rows into a Sheet. After the submit ...
2
votes
1answer
90 views

Google Sheet conditional formatting, highlight just 1 cell in a range based on another cell [duplicate]

How can I highlight a cell in a range, if its value is equal to a different cell? Example: Random numbers in range A5:A20. I want to highlight a cell in this range if it is equal to B4. [ A ...
2
votes
1answer
53 views

An answer in column B on Google Sheets generated from a Google form sends email to collaborators including all data in that row

I'm a school teacher using Google Forms and would like to generate an email to myself, and if possible other collaborators, based on an text answer of "YES" in column B from the sheet generated by ...
2
votes
1answer
48 views

Google Script Replace Function Reference

Is it possible to build a Google Script where I can select a cell, take its function, replace the cell references (instead of C2,C3,C4, etc. make it AC1,AC2,AC3, etc.), and then return the output of ...
2
votes
1answer
294 views

View and edit data in Google Spreadsheets in a form

Question: Is there any way to view and edit data in Google Spreadsheets in a form, like Microsoft Access or OpenOffice Base can do that? It would be perfect if you could cycle through each record, ...
2
votes
1answer
34 views

Best way to iterate through all input arguments?

I am writing a custom function in sheets that will not have a defined number or format of arguments. As an example, the call might be: =myFunc(a1:b3,a2:a4,b1:b9,c1,d2, [might be more, might be less]) ...
2
votes
1answer
50 views

Changing colour of row if a certain aspect is met

I have a list of clients that send in certain information to me and I have setup columns to enter a tick using (=char(10004)) on R,S,T,U,V,W,X & Y columns and I need to colour them to go green ...
2
votes
1answer
46 views

Highlighting duplicate data in rows

I have a query and I cant figure out why it's not correct. I have two lists of data and I want to highlight the matching data in the separate lists. Below is the query I have constructed ...
2
votes
1answer
68 views

Customizing filter in Google Spreadsheet

I'm trying to create a Spreadsheet for work purposes but I can't get it the way I want. If I do it in Excel it works but I can't match it in Google Spreadsheet. I don't even know if it's possible. ...
2
votes
1answer
150 views

Multiple cells with same drop-down list, but disallowing duplicate selections

I'm looking to create a few drop downs in my spreadsheet where they share the same list of choices, but you can't select the same option twice. This is for choosing first, second, and third choices ...
2
votes
1answer
26 views

Google Sheets “Skipping blanks”

I have a huge data sheet that is being updated every week. It has about 2,000 colums and 6 rows. Every cell each week is new (Its a list of average prices, so the names of the items remain the same ...
2
votes
1answer
39 views

substitute() multiple terms in Google Spreadsheets

I'm trying to simplify semi-complex text strings into much simpler ones - specifically names into hashtags. For example: Tomáš Rosický into #TomasRosicky or Alex Oxlade-Chamberlain into ...
2
votes
1answer
71 views

Using HLOOKUP for date values

I'm trying to get the HLOOKUP function working in my sheet but it's not working like it should. The function is: =HLOOKUP(TEXT(NOW(), "yyyy-MM-dd"), INDIRECT(TEXT(NOW(), "MMMM") & "!B1:AE5"), 3, ...