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
0answers
2 views

Google Sheets: How do you perform a bulk find & replace or extract & replace using a list of search phrases and their replacements?

This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated. I have been looking for a solution that searches for one or multiple strings in each of ...
0
votes
0answers
10 views

Keep Cell reference in a formula fixed while adding columns to a sheet of data [duplicate]

I have a spreadsheet with multiple sheets, and many rows and columns of data. I like to count the number of each occurance of every value for the last "x" values entered. The problem is ...
0
votes
0answers
16 views

Conditional formatting with multiple conditions (lost between IF or AND, and not empty cells !)

I am trying to combine formulas here, to put color on specific cells (out of a specific range) when they are not empty, and when a checkbox in another cell is checked. Here is what I tried : =AND($D4=...
0
votes
0answers
16 views

How to Sum same values

How to Sum with same values using vlookup and importrange Sheet 1 Rey 1200 Rey 700 Dan 500 Dan 1200 Sheet 2 Rey 1200 (it s/b 1900) Dan 500 (it s/b 1700) using vlookup & importrange the first row ...
0
votes
2answers
16 views

Alternative to a spreadsheet / Google Sheets for distributing one-time-use links?

I have a list of 1,000+ unique URLs for people to download a custom iPhone app that's on Apple Business Manager. Google Sheets allows everyone with the URL of the sheet to see the list, and they can ...
0
votes
0answers
10 views

How to match 2 cells for get a third cell

In the first sheet, I've in the row A the column of the users, I've in row B the column of the users and the column C the PC hostname of the users. In the second sheet I've the firstname and lastname ...
0
votes
1answer
27 views

Table transform

I have a table: Each ID is double on the 'ans' and 'num' lines I need to convert to this. Here 'ans' and 'num' are the field names and the values below. Maybe you've already had a case like this and ...
0
votes
1answer
17 views

Filter by merged cells in Google Sheets?

I need to filter a data table by date values. Unfortunately the date cells are merged like so: So if I would write =FILTER(B1:B; A1 >= DATE(2020, 4, 1)) I only get every other value in Column B. ...
0
votes
0answers
26 views

Getting column data and re-arranging it in rows, with a pattern, using formulas

In a worksheet of multiple sheets, I have Sheet1, e.g. with the following: (these rows will be less or more and are manually entered) Sheet1 A B C 1 APPLE ...
0
votes
1answer
20 views

How to import data from a webpage to process in Google Sheet

Here is the image and I would like to retrieve the 6 numbers and import it to a Google Sheet.
1
vote
1answer
29 views

Google Sheets data formatting and extractions into more workable, easier to read format

I have a set of data representing visitors and times of their visits. Each entry is in its own row in the sheet. Example; Time of the visit in A1 and Name in A2. The single digit numbers represent ...
0
votes
0answers
14 views

how to get current row number in google sheet

I have to get the current row number of a column in google sheet. like, someone writing in C4, then in D4, my function will be: DIVIDE(C4, 2), and in E4, SUM(C4, D4) that means i wanna update row by ...
0
votes
0answers
14 views

Google Sheets allow only one unique entry after VLOOKUP

=if(isblank(D2),"",vlookup(D2,Master!$B$1:$F$600,5,false)) I have this working which looksup a UID value to select a user. The users name is returned to the cell. The problem is now I'd ...
0
votes
0answers
10 views

How to select and remove drawing object in macro

I have a Google Sheet template with a button that triggers a macro to make a new copy of itself that can than be edited (code below). I'd like the macro to remove the button on its copy, as there's no ...
1
vote
2answers
28 views

How force Google Sheets to accept the text as TEXT FORMAT (and stopped autoformatting as number)

For example, when I paste: +XYZ in Google Sheets, it autoformats it: I've tried different things (Format>Plain Text) but it doesnt work. I just want to get what I paste there as plain text. (i ...
1
vote
1answer
47 views

Search multiple columns for one value

This is roughly what my table looks like: A B C D E F Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
2
votes
2answers
26 views

Case statements in SWITCH statements in Google Sheets

I'm updating a Google Sheet. In that Google Sheet, I need to display a student's grade based on specific cut offs. I thought a SWITCH statement would be the correct approach. However, based on that ...
0
votes
0answers
7 views

How do I write a script that exits a cell that is being edited

If I enter a value in cell A1 without exiting the cell 1. Before I exit editing A1 I run a script that at some point selects another cell B1 2, while A1 is still being edited. When I then click C1, ...
0
votes
0answers
27 views

Data Scrape to Google Sheets from MyFitnessPal.com

I would like to be able to pull the macronutrient (and more if possible) data from MyFitnessPal using what i assume would need to be an importhtml() or importxml() function directly onto a google ...
1
vote
1answer
18 views

Filter causing Circular Reference Error - Not obvious why?

I have 3 sheets. SheetA and SheetB contain data and a third sheet where I want to merge the contents of the SheetA and SheetB in it. I type this formula in the result sheet (the 3rd sheet): ={filter(...
0
votes
2answers
34 views

Google sheets array formula for taking average every Sunday and output is in same row corresponding column

I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday. If a cell in B is left blank then it should ...
1
vote
1answer
14 views

Duplicate row values to next row, based off specific column

I'm trying to set up a button that will copy information from the last row and paste it into the row right underneath (based on where column D last has a value). Almost like a "duplicate" ...
1
vote
0answers
13 views

How can I get notified of changes to certain columns in google sheets

Zapier only provides functionality to monitoring rows. How can I monitor changes to only specific columns? function sendEmailAlert() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var data = ...
1
vote
1answer
15 views

Is it possible arrayformula combined with many if, and, isblank?

Is it possible arrayformula combined with many if, and, isblank ?? The code below works fine =IF(AND(ISBLANK(F8)=TRUE,ISBLANK(H8)=FALSE), "Not shipped", IF((AND(ISBLANK(F8)=FALSE,ISBLANK(H8)=...
0
votes
1answer
18 views

Conditional Formatting Based on another Cell with Multiple IF's

I had a Conditional Format in Place, but it somehow stopped working once I filled in an IF Argument into my cell. A1 Contains a Number I put in manually A2 Contains another Number I put in manually A3 ...
0
votes
1answer
16 views

Calculating monthly average from daily average (grouped by month)

I have a spreadsheet with three columns: one for dates, one for daily income and one for daily change rates. I want to have a sum of all the daily income grouped by month as seen above. For that, on ...
0
votes
1answer
21 views

Reducing amount of IMPORTHTML Requests

Main Question I'm trying to reduce my IMPORTHTML requests. The main issue is I'm forced to do the process twice. =INDEX(IMPORTHTML(*Link*,"Table", 1),ROWS(IMPORTHTML(*Link*,"Table",...
0
votes
0answers
21 views

Google Apps Script: Appending data to a Spreadsheet if it is already not present

I am new to Google apps script and I am trying to write a program where whenever I scan a data. The scannedData should only be appended to the spreadsheet if and only if the data is not already ...
1
vote
0answers
14 views

Removing Last Updated value when No data was selected from the column to check

I need help on my Google Sheet script. It is working fine on showing the timestamp of Last Updated at colB every time a new Call Status was set at ColA ; but when I deleted the status at colA, the ...
-1
votes
0answers
18 views

script for a button to add value of cell to existing value of cell on another sheet, google sheets

I'm trying to make a button on a spreadsheet for an apparel manufacturing business, I have one sheet that we use for stock on hand that then feeds requirements into two other sheets for cutting and ...
0
votes
0answers
13 views

A Script that Allows a Checkbox to Recognise a Value via Formula and Copy to Another Cell

Is it possible for: a set of checkboxes to recognise lists (grouped in one cell) on entry (via formula) to then have the same checkboxes copy the list to another cell as it returns "TRUE" ...
0
votes
0answers
16 views

How to delete old rows in Google Spreadsheets?

Hi i have a google sheet that collect tweets from twitter. i need a function that should delete rows that are more then 2 weeks old. here is the sheet https://docs.google.com/spreadsheets/d/1Zhhl6-...
1
vote
0answers
24 views

Link audio files in Google Drive folder in a Google Sheet

I have hundreds of audio files (.mp3) in a Google Drive folder that I need to list with links in a Google Sheet. Very similar to the problem described here, but I was not able to adapt the code to ....
3
votes
2answers
46 views

How to get a difference of “X years, Y months, Z days” between two dates?

I have two dates: 29/05/2020 and today. How can I get a date diff of these 2 dates, in a meaningful format such as "Z years, X months, Y days" separating these dates? Ideally this should ...
-2
votes
1answer
28 views

How to select multiple individual cells on Google Sheets on my Android mobile phone?

How to select multiple individual cells on Google Sheets on my Android mobile phone? Example: On Windows it is possible by using the CTRL button + left mouse button click, but I did not find any ...
0
votes
0answers
26 views

Google Sheets: Link/Sync Two Cells Dynamically (based on a third column's data)

I want to preface this by saying this is NOT a duplicate of Sync two cells value. It's similar, and uses the OnEdit() Function of Google Sheets, but the solution presented in that thread only works ...
0
votes
1answer
10 views

weird bug(?) with getValue returning “You are trying to edit a protected cell or object”

This is just...bizarre... I'm running a function with a user that does have full Edit access to the Sheets in question. This code: function test() { var ss = SpreadsheetApp.getActiveSpreadsheet();...
0
votes
1answer
37 views

Why does Google Sheets Conditional Formatting not apply properly to all cells?

Alright, so I've been having this issue for a few hours now and am confident to say that I can't fix it by myself. I've been playing around with Google Sheets trying to apply Conditional Formatting ...
0
votes
1answer
26 views

In Google Sheets how do I search for a specific value in a column?

I've started working with Google Sheets. I'm looking for a value that I believe exists in a specific column. I know which column the value exists in. How do I find the value?
1
vote
1answer
36 views

Autofill Google sheets template based on new entries in column A of “master” sheet

I am a teacher and I keep my student data on a Google sheet. I have a "Student Data Report" template that will automatically copy as a new sheet with the student id as the sheet name as I ...
1
vote
1answer
73 views

Google Sheets: How to query until reaching a particular sum?

I have a sheet with a lot of data, but to keep this simple let's say column A has a product, column B has a date, and column C has an integer which can be blank, 1, or an integer greater than 1: ...
0
votes
1answer
25 views

formatting currency to be used in an email

I'm summing a range from a Google Sheet, then putting the results in the body of an email as currency. But I'm running into formatting issues that seems surprisingly hard to deal with. The range of ...
0
votes
2answers
45 views

concatenation truncating numberic value

in a cell J21 I have entered a numeric value 1.10000. The cell is set to numeric value with 5 decimal places. When using concatenate =CONCATENATE("=CONCATENATE("@ £/€ rate: ",(J21)) in ...
0
votes
1answer
23 views

Can you define a conditional format range without an absolute reference?

I would like to conditionally format a column in my sheet, regardless of how long the sheet is. This is because new rows are routinely inserted, and the sheet gets longer but the conditional ...
1
vote
1answer
37 views

What is “Open in AppSheet” option?

Today I noticed new option in Tools menu of Google Sheets: "Open in AppSheet". For one spreadsheet it's inactive: For other spreadsheet it is active: What is the purpose of this new option?...
3
votes
1answer
46 views

How do I reference a cell for the sheet name in IMPORTRANGE function in Google Sheets when the sheet is in another workbook?

I have two workbooks with several sheets in each. I have a dropdown to select the sheet name that I'm using for reference for URLs and to reference other sheets in the main book. I successfully used ...
0
votes
2answers
33 views

How to pull data from one sheet to another

Very inexperienced google sheets user here. I have a sheet with employee data. One row and many columns of data for each employee. Some employees appear more than once (i.e. in multiple rows) I need a ...
0
votes
0answers
28 views

Removing values in array without filter() in Google Sheets

I want to remove specific (or any) values in dynamic array/range and return the result of the resized array without those values without using filter() as I need to use arrayformula. For example, {&...
0
votes
1answer
24 views

How to apply DATEVALUE to column

I have two columns with extracted date values. I want to add those two values together and apply to the whole column. The data looks like this: AM AN AO AP AQ date time ...
0
votes
0answers
3 views

Using a cell to build a formula

I am trying to build an ever changing formula, that will list the contents from multiple work sheets. The trouble I have is sheets will be constantly deleted and added to the spreadsheet, meaning I ...

1
2 3 4 5
127