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

learn more… | top users | synonyms (2)

1
vote
2answers
47 views

Row by row comparison with FILTER returning error

I am trying do a row by row comparison of two separate sheets, and display the unequal rows on a third sheet. As an example, rows three and five on Sheet1 are different from Sheet2 and I want to ...
1
vote
1answer
21 views

What is a cut and paste approach to converting a chunk of a google spreadsheet to markdown table?

I maintain my web site in a version of markdown, with the common table extension. I maintain my inventory as a google spreadsheet. I'd like a quick way to copy paste a chunk of a spreadsheet, and ...
1
vote
1answer
12 views

Google sheets formula to countunique rows with a condition

I have a sheet where I keep track of some expenses. the data is formatted like this: Vendor A | Plumbing | $250 Vendor B | Cleaning | Vendor A | Cleaning | $19 Vendor C | Washing | $20 | ...
1
vote
2answers
39 views

An arrayformula to find the previous non-empty cell in another column and find the last row to work with

I would like to do something similar to what was solved in the question: An arrayformula to find the previous non-empty cell in another column The problem was: If I have a column "input", I would ...
1
vote
1answer
39 views

Conditionally format data in spreadsheet column, but only when it changes

Say I have this data in a Google Spreadsheet: cat cat mouse mouse mouse cat dog dog And I only want to apply conditional formatting to the ones in brackets [] (so I can hide them but still ...
1
vote
1answer
50 views

Is it possible to use the contents of a cell in a custom conditional formatting rule in Google Sheets?

For example, given the sheet below: I'd like the background colours of columns B-D to change automatically when I update the values in column A Is that possible? I'm looking for a solution that ...
1
vote
1answer
75 views

Google Apps Script Exceeded maximum execution time

My code for an API pull has been timing out a lot lately. Here is my code (note that I have changed some variables for privacy): // SHEET DATA var ss = SpreadsheetApp.getActive(); var data_sheet = ...
1
vote
1answer
24 views

Apply formatting and formulas to another sheet?

I have a template tab that contains all formulas and formatting. Then one tab for each user. I know how to reference cell content with =template!A1. How to reference formula that reference current tab?...
1
vote
1answer
26 views

Using a second table to determine the value of a cell in the first table

I have a Google Spreadsheet with table 1 'city, country, region'. On a separate tab there is table 2 'countries and regions'. What statement can populate the 'region' column in table 1, based on the ...
1
vote
1answer
15 views

How can I get the halfway date of two dates in Google Sheets?

I have two date columns in mm-yy format and I need to know the halfway date of these two. What formula do I use? I tried simple subtraction of the two dates and then divided it by two but it didn't ...
1
vote
1answer
28 views

How can I force a Google Spreadsheet to have column in one tab have the same width as the same column in a different tab?

Is there any way to force a Google Spreadsheet to have column i in tab j of same width as column i in tab k? In other words, can I configure Google Spreadsheet so that a column width change is always ...
1
vote
1answer
21 views

Remove any sheets not listed in an array

I have a Google App Script that has 6 sheets. onOpen, I would like to see if the user created any additional sheets, and if so, delete them. So far, I have: function onOpen(){ var ...
1
vote
1answer
68 views

Restricted view of a sheet, with each person seeing only their rows

I created a Google Spreadsheet. Column B contains the name of my employees. In Column C there is a task for every employee. Different employees need to have access to this document, but I prefer my ...
1
vote
1answer
123 views

Getting values from other sheets with a custom function

I've defined the following function, and use it in sheet1: function myfcn (sheetname,rowstart,rows,columnstart,rows){ var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); ...
1
vote
1answer
57 views

How to receive a notification when changes are made to a specific tab in Google Sheets?

If would like to receive an email notification if a new row is added to a specific tab in a Google Sheets. The default notification rule settings are too limited and the Google Sheets Add-on Store ...
1
vote
1answer
163 views

Referencing a range in a sheet with variable name in another cell

When I would like to import (query) many columns in another sheet I use this code: =QUERY(sheet2!A1:D1;;) Now I would like to make this formula a little dynamic making sheet2 variable. So I put the ...
1
vote
1answer
64 views

How should I automate graph generation and image output in Google Sheets?

I am trying to send graphs of data to email recipients. I would like to output images of row-specific graphs based on cells in a Google Sheets document. In combination with Google Sheets/Drive, I am ...
1
vote
1answer
77 views

Drag and drop open csv in Google Sheets

The current process for opening a csv file in Google Sheets is cumbersome. Open Google Sheets > New Document > File > Import > Upload Tab > Drag in File > Select 'Replace current sheet' Would love ...
1
vote
2answers
43 views

How do I sum a column of cells by date?

I have a spreadsheet with these data (all of these dates and amounts are arbitrary): A B 12/1/2015 700 1/7/2016 100 1/13/2016 200 2/5/2016 300 3/19/2016 500 and another ...
1
vote
1answer
51 views

How do I add blank rows after each item from a FILTER() or QUERY() result?

Suppose you have a spreadsheet similar to: A 1 "text a1" 2 "text a2" 3 "text a3" 4 "text a4" ...and you'd like to create a new column containing each item followed by two ...
1
vote
1answer
44 views

Google Sheets overtime calculation if/and/or statements

I'm trying to set up a simple bi-weekly paycheck estimator in Google Sheets. Basically you put in the hours worked for Week 1 and Week 2, and I want it to automatically determine the amount of ...
1
vote
1answer
52 views

Google Sheet get value from a REST API

I need to include in a cell on a Google sheet the value of the ticker.last attribute in a JSON which is being constantly updated from a REST API located at https://www.mercadobitcoin.net/api/ticker/ ...
1
vote
1answer
414 views

Is there a way to generate edit response url for google form

I have a Google Form With a Google spreadsheet to store responses. In my spread sheet I have 4 column: name, email, revenue, and a fourth column Id which is used to identify the particular recipient. ...
1
vote
1answer
30 views

Keeping track of prices, recording every change above some threshold

An updated price comes in to a cell each minute "googlefinance()". In a second cell we start with a reference price which is typed in manually. The initial value is equal to the first price that comes ...
1
vote
1answer
87 views

“sort” function results in all of my data going to the bottom of the 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. I would like to be able to have any staff member add jobs that need to be taken,...
1
vote
1answer
29 views

How to turn a month string into a month int in Google Spreadhseets

I am trying to take the name of a month, such as October and turn that into the corresponding number for that month 10. Past making a long formula that checks for each month by string, and without ...
1
vote
1answer
42 views

Conditional formatting based on comparison with today's date and also with a future date

I'd like to conditionally format cells that will go red if it's past its expiration, go yellow if it's within 3 months of expiring, to remain green if date is clear of expiry. For example: today's ...
1
vote
2answers
34 views

Google spreadsheet: reformat event data (several dates on one row/event) to create a .csv file (one date per row/event)

I currently have a list of several recurrent events, with dates of recurrence on the same row. I hope to create a .csv file for importing into calendars, so would like to have each recurrent date on a ...
1
vote
1answer
21 views

Formatting work schedules in an easy-to-process, and easy to enter way?

I'm trying to format work schedules in google spreadsheets in a way that makes them easy to process. I have a sheet that will tell you who is supposed to be here on what day, who was gone, and ...
1
vote
1answer
28 views

FILTER() Is not returning consistent results

I'm having some issues with FILTER() not returning consistent results, below is an image detailing the inconsistencies. When filtering for 3:30 PM I only get 2 results instead of 3. When filtering ...
1
vote
1answer
18 views

Select a sheet to print from, using a dropdown

I have a "workbook" with multiple sheets. Each sheet is a schedule for the week for employees with a bunch of math stuff to help a general manager see their labor. I'm trying to create a single sheet ...
1
vote
1answer
85 views

Google Sheet not being populated and responses summary not getting incremented after Google Form submission

My wife has a paint & wine party business and I've been using an embedded Google Form for registration on her site for nearly a year without issue. Last week I updated the photos in the ...
1
vote
1answer
34 views

RegexReplace produces an error

I have two sheets of data as per below: ╔═══════════╦══════╦══════════════╗ ║ SheetName ║ Cell ║ CellValue ║ ╠═══════════╬══════╬══════════════╣ ║ Sheet1 ║ B1 ║ ='sheet2'!A2 ║ ║ Sheet2 ║ ...
1
vote
1answer
325 views

Dynamic generation of sheet name from cell content

Is it possible to dynamically change the name of a sheet in Google Sheets based on the contents of a specific cell? For instance, I'd like my sheet to get its name from whatever is in cell A1... is ...
1
vote
2answers
474 views

Google Sheets - Conditional Formatting with IF and THEN

I would like to create a daily dashboard with cells that show the progress of my work regarding to a date. For example: A1 is a text saying "Open". C1 is a cell that contains a date. This date ...
1
vote
1answer
143 views

Google Sheets Customize View Using Create Custom Menu and Hiding/Showing Specifc Columns

Trying to determine a way that I can use a custom menu (.createMenu) with views that will allow me to show / hide specific columns. I need the capability to pick specific multiple columns and not ...
1
vote
1answer
168 views

Report/process only using the last entry made by the user in a Google form

I have a form where students can enter choices: for instance the specialisation they want to pursue, and the semester when they will start that specialisation. An example entry form is here, but it ...
1
vote
1answer
20 views

How to select all cells between two cells, where more may be inserted later?

I am trying to count all the cells that have information between two cells that are in the same column. This is easy enough to do using =countif(cell1:cell2, "<>") the problem is that I may ...
1
vote
1answer
41 views

Add cells based on the content of another column

I have this sheet and I am trying to add the values of June but the issue that I have is that these values will update often with so I need to find a formula that accounts for that. The other issue is ...
1
vote
1answer
50 views

Conditional Formatting: no color when cell is empty

I am using this custom formula for my sheet: =if(B2<((B$7/4)*$A2), 1, 0). However, this also highlights the rest of the cells that are empty. How do I exclude empty cells from this formula?
1
vote
1answer
59 views

How do I organise my data in Google SpreadSheets

I have separate sheets based on month's of the year. Each time I create a new month I copy the template sheet and fill in the data. Therefore each sheet has the same layout it's only the data that ...
1
vote
2answers
127 views

Google Sheets permissions

I built two separate spreadsheets in Google Drive and spreadsheet "A" has a script that when run, will copy the input data into spreadsheet "B". When I test it, it works perfectly. But I have other ...
1
vote
1answer
264 views

How to create an auto updating Google spreadsheet with information from Steam?

How would I create an automatically updating Google spreadsheet containing information about Recently Played in Steam? Table must look like here: A2 taking information about account name from here:...
1
vote
1answer
39 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 So,...
1
vote
1answer
50 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
2k views

Query(ImportRange) throws “Formula Parse Error”

I'm working with Google Spreadsheets and I've run into a wall with a query(importrange) function. =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,...
1
vote
1answer
34 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: +------...
1
vote
1answer
222 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 ...
1
vote
1answer
259 views

Google Spreadsheets updating after every change, which is too often, slowing everything down

I have a lot of independent functions and information in my sheet, and every time I make a slight change somewhere in the sheet, it seems like the entire sheet is being saved and re-updated. So every (...
1
vote
2answers
74 views

How to insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets

How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets? Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say "...