2022 Developer Survey is open! Take survey.

Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
0 votes
1 answer
20 views

How to make a formula expand to new rows when new data is added?

I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
user avatar
1 vote
2 answers
18 views

Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets

PROBLEM Since Google Sheets doesn't natively support JSON data, which I'm importing, I'm trying to split the data into a matrix of four columns and an unfixed number of rows using only Google Sheets's ...
user avatar
  • 181
0 votes
0 answers
14 views

Can I add another, different set of data to a row or column in the same sheet?

I am trying to enter some analytics data from Twitter. I want to sort things by month. In each month, I want to have columns for 1st best tweet, 2nd best tweet, etc. In each of these tweet columns, I ...
user avatar
  • 1
0 votes
0 answers
9 views

How to create a linked sheet that checks a box for a day if found and inserts text from another column

I'm trying to create a sheet in google sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
user avatar
0 votes
1 answer
16 views

Flattening the values (not formulae) of a summary sheet

I work at a company that owns a lot of film gear that they bring on freelance shoots. I'm trying to create a simple, streamlined way for them to notate what's brought on each shoot so that they don't ...
user avatar
0 votes
0 answers
12 views

Why isn't my formula working?

I am using the following formula: =ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('Pick Your Meals'!$E$1:$BIF$1,SMALL(IF('Pick Your Meals'!$E$130:$BIF$130>0,COLUMN('Pick Your Meals'!$E$1:$BIF$1)-COLUMN(...
user avatar
0 votes
1 answer
32 views

Creating a dynamic 2d array

I have to create variable length sequences from a column along its rows. Eg- Suppose the column is as below 2 4 3 I want to create a sequence like this 2 | 1 | 2 4 | 1 | 2 | 3 | 4 3 | 1 | 2 | ...
user avatar
  • 1
1 vote
1 answer
23 views

Choose format for new Date() in Google Apps Script

I have tried almost all suggestions on the internet, yet nothing seems to work. Maybe someone can help a newbie out. I am using this at the end of my script: sheet.appendRow([new Date(), null, text]); ...
user avatar
1 vote
2 answers
22 views

Changing the dot (.) into comma (,) in Google Sheets, how do I do that? (numerical pad, in Holland, for currency)

I already managed to change the dot on numerical pad into a comma in i.e. excel. But how do I do that in Google Sheets?
user avatar
3 votes
1 answer
119 views

Formula for matrix (dates in descending order)

As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") ...
user avatar
  • 199
0 votes
1 answer
29 views

How do I simplify this formula to be expandable?

The Example sheet The formula: For all x from 1 to 10, if (Sell <= Buy * x) then x, else ">=10" I thought I might be able to cheat a little with the sequence function: =ARRAYFORMULA(...
user avatar
  • 3
3 votes
2 answers
113 views

Formula for matrix (dates in ascending order)

Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts) Let's say I have a table that is updated daily showing the fruit ...
user avatar
  • 199
0 votes
1 answer
25 views

Google sheets: Single out certain words in string based on second string

I have one column with concatenated words, say international city names. Each cell in this column is different, but city names overlap. And I have one cell with concatenated words, say only German ...
user avatar
  • 1
0 votes
0 answers
21 views

How to edit/format/change date forat sequence of a existing data into another date format sequence?

I got a bank account statement xls sheet with date in format of DD/MM/YYYY i.e. 06/01/2019 in the original form, but when I'm trying to format it into DD-MON-YY it is taking the value as MM/DD/YYYY ...
user avatar
1 vote
3 answers
39 views

Cell specifying the running total length

Context Here column C contains a running total of the last 5 items from column B. Formula The formula is straightforward: Question In the above, it's hardcoded for a running total of 5. What's a ...
user avatar
0 votes
0 answers
22 views

How do I make a points system on Google sheets?

I have a timetable where people can write their names into a box which correlates to a different duty they must complete I want to assign a different value of “points” to each duty so that when a ...
user avatar
0 votes
1 answer
23 views

How do I keep exact text formatting when using a data validation dropdown list in Google Sheets?

I have a column that contains a bold number and a non-bold text part. I want to use this information as a drop-down selection list, but it currently isn't letting me keep the bold formatting on only ...
user avatar
0 votes
3 answers
26 views

Making a graph from data of one out every n lines

I have data that comes in batches of N values ("Y" values if you like) for a given index (or "X" value if you like). How to make N separate series in my graph for each entry of a ...
user avatar
  • 101
0 votes
0 answers
13 views

Google Sheets Margins in Print Settings Significantly Different than Printed PDF

I need to generate a PDF from a sheet in Google Sheets and I need the margins to be exactly as depicted in the print settings preview. As you can see in the picture below, the preview is differing ...
user avatar
0 votes
1 answer
33 views

How do I put the if function inside the lookup? - Daily Routine sheet

I have a sheet - daily routine which has two columns: activity and time as shown here Activity Time Sleep 😴 6:00 am Toilet 🚽 6:15 am Get ready for gym 🤸‍♂️ 6:30 am Exercise 💪 7:50 am ... ...
user avatar
  • 101
0 votes
0 answers
16 views

Insert Dropdown (Cell Range) from Google Sheets into Google Doc

I'm using Google Docs to collect a lot of information about property. Each property has its own Doc file. In this Doc, I have various Dropdown lists. I'm at the point where I need to update and add ...
user avatar
  • 101
0 votes
1 answer
17 views

Google Sheets - Is there a way to filter values but keep them in the respective rows they came from?

I'm looking for a way to filter values based on another column but keep them in the same rows they came from. Here's an example I created manually of the correct output: The goal here is to have a ...
user avatar
1 vote
2 answers
27 views

Google sheet, from 2 lists to flattened table

I have two independent columns with data. How could I use that data to then produce a flattened table like this:
user avatar
  • 113
1 vote
2 answers
29 views

Google sheets function to filter out lowest associated cells

I was wondering if there's a way to use a function (maybe Filter?) in order to sort this data such that column K only contains one of each number, and the highest test associated with it in column L. ...
user avatar
0 votes
0 answers
17 views

How to approach this? Which formula should I use?

I am working on a Google Sheet in which I am classifying a "product" with an id number and I want to pull out data from another sheet that contains 2 things: confirmation of the id type of ...
user avatar
0 votes
2 answers
28 views

How to write a Google Sheets formula to extra a substring from a URL?

I'm trying to set up a formula to extract data from a URL that is in one of the column B of my Google Sheet. I've been tryin use the LEFT formula to extract the data, but the problem is that the ...
user avatar
2 votes
1 answer
67 views

"Copy to" confusion

In my spread sheet, there are 3 tabs, let's call them A,B and C. Tab A has user input data Tab B picks up that data (get.range and set.formula) and time stamps it (daily interval) with rows appended (...
user avatar
  • 199
1 vote
0 answers
24 views

Filter date, and add blank rows between different time slots

I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day. Here is a ...
user avatar
  • 11
0 votes
1 answer
15 views

Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage

I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks: As you can see, the task bar currently highlights gray dependent on the start and ...
user avatar
1 vote
1 answer
31 views

Text input if cell > than 1

I would only like the values in Column A to return if there is a value of >1 in Column B - I am unsure on a formula to do this
user avatar
0 votes
1 answer
13 views

Google sheets indirect formula question

I have a list of students that in Column A has their first name and then in following columns has their specific data that I am interested in. I have removed last names and data as it is sensitive ...
user avatar
  • 1
1 vote
1 answer
49 views

Appscript: Compare a cell Value pre & post update to populate another cell

Newbie to scripts. Link to the sheet with minimal essential information In the status tab, there would be an inventory of equipment (currently only one (A1). The equipment can be in various states (...
user avatar
  • 199
0 votes
0 answers
17 views

Google Sheet Crashing Error Code: Out of Memory or RESULT_CODE_HUNG

I'm a documentation manager and have a Google Sheet that serves as the primary tracker of my team's active pipeline of documents. The file is quite large and includes a lot of formulas and scripts to ...
user avatar
0 votes
1 answer
28 views

Wikipedia List Scrapping of Multiple Lists

I am trying to scrap a list using the formula below, but somehow, after 2,3, and I get the following error on the Google Sheet. "a Formula Wrong number of arguments to IMPORTHTML. Expected ...
user avatar
  • 49
3 votes
1 answer
51 views

Running onEdit script only in certain sheet

I'm having an issue with one of my scripts created from a macro. I want to update a filter onEdit but only when a certain sheet ("Invoice") is open. Here's what I have now that works across ...
user avatar
  • 31
0 votes
0 answers
21 views

Collecting crowd-sourced data in tabular or spreadsheet format

I'm involved in a citizen-science project, polling recreational anglers about their fishing locations on the ocean (lat-lon), various characteristics at each location (depth, what species they catch, ...
user avatar
0 votes
0 answers
34 views

Trying to auto-populate cells based off a dropdown selection

I currently have a spreadsheet that is a template for data entry. For the first column of input, it is a Y/N question-- if the answer is Yes, then there are other fields of entry for columns E-J. But ...
user avatar
  • 1
-1 votes
1 answer
60 views

Getting a count once certain criteria are met [closed]

I have a sheet that keeps track of files and their corresponding boxes. I am looking for help with a formula that will keep a count of how many boxes are completed. In the column "Box #" is ...
user avatar
  • 1
0 votes
2 answers
41 views

Formula that Does NOT Contain 2 Different Texts

So I'm using Google sheets and I would like a cell to conditionally highlight when the cell does not have 2 specific texts. So in my example, I would like cell B26 to highlight ONLY if it does not ...
user avatar
0 votes
1 answer
27 views

I need to update the app script to also capture Gmail attachments' name along with other details

First of all, I am not familiar with coding. I have a code that I use to pull certain information from Gmail based on the labels. Now the problem is, the code does not include a line to have the name ...
user avatar
  • 1
0 votes
1 answer
26 views

Google Spreadsheets Charts: Drawing continuous chart based on multiple data ranges

I have a big set of data that I split it into 2 ranges (here I am just showing small data for clarification). I want to draw one chart based on those 2 ranges, to get a result as the "Desired ...
user avatar
  • 101
0 votes
1 answer
27 views

Google Sheet Regex URL Replace

Looking for regex or another solution to update all the URLs without HTTPS at the start of the domain; for example, domain.com to > https://domain.com. Please, note I have multiple domains in the ...
user avatar
  • 49
-1 votes
1 answer
21 views

Is there a way to use URLFetchApp to behave like importData for CSV rresults

For various reasons (see question Force the refresh of a Google Sheet) I am trying to replace the use of importData in an app script with a custom function that leverages UrlFetchApp.fetch() retrieve ...
user avatar
-1 votes
0 answers
17 views

Query error on import range [duplicate]

I am importing data from another sheet. I am trying to not import rows that have a null value in a specific column. But no matter what I do it still imports the null row. =QUERY(IMPORTRANGE("...
user avatar
0 votes
1 answer
20 views

How to conditional format in Google Sheets based on a external resource

I have a Google sheet that has a value of a stock I bought in the past. The page was stark with just white cells and black text. So I decided to brighten it up with some colour. One thing I want to ...
user avatar
0 votes
1 answer
23 views

Google sheets/Excel: Rolling average for the past three months on different sheets [closed]

I was tasked with creating a rolling average column which compares one row to it's exact matches from the last three months (each month has it's own sheet). Currently, my (beginners) work looks like ...
user avatar
  • 1
0 votes
2 answers
25 views

Set cell to reject wrongly formatted data

I’ve got a “time tracker” Google Sheet where people need to enter a record of a start time and end time; we then have a formula which works out the duration between the two times. The formula is ...
user avatar
  • 6,143
0 votes
0 answers
21 views

People Chips in Google Sheets

When I convert my email addresses (@) to People Chips, the chip is still an email address in format. How can I make it a chip but only with the person's name and not the entire email address?
user avatar
0 votes
0 answers
20 views

Why does my formula stop working after 1000 rows in Google Sheets?

I am working in Google Sheets and have the following formula: =ArrayFormula(IF(ROWS(A$5:A5)>$C$2,"",INDEX('Annual Schedule'!$A$1:J$1852,SMALL(IF(('Annual Schedule'!$A$1:J$1852>=$A$2)*('...
user avatar
  • 1
0 votes
2 answers
28 views

COUNTIFS in Google Sheets

I have been battling to get the COUNTIFS formula right for the below. I need to count the number of events per day. How many times did the event happen between 2021-04-20 14:24:54 and 2021-04-20 19:16:...
user avatar

1
2 3 4 5
158