Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.

-1
votes
1answer
28 views

How to return cell address? [duplicate]

Basically, I have this function that finds the last non-blank cell in a range while skipping columns I don't want it to search. The value is returned, but I want it to return the cell address (for ...
0
votes
0answers
8 views

Change default PDF filename when printing from Google Sheets

When I try to print from Google Sheets, Google automatically creates a PDF that is named after my file with "- Sheet1" appended to the end. Is there any way to change the default so it doesn't append ...
0
votes
2answers
44 views

How do I find the address of last non-blank cell in a row (while skipping columns)

Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the ...
1
vote
2answers
25 views

Next Payment Date

I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
1
vote
0answers
85 views

IMPORTXML to import attribute = “Error: Imported content is empty”

Google sheets is giving me some trouble in retrieving span style attributes. I am trying to scrape a site (actually this) to grab some icon images for my spreadsheet. Below in red is one such example ...
0
votes
1answer
14 views

If checked, add row to data chart?

Basically, I have been working on some automated solutions to show the date and data for the most recent 4 weeks on this data sheet. There are still some bugs to work out, but the last thing I need to ...
1
vote
1answer
10 views

Restrict entry to a group of cells based on the value in another cell

I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
0
votes
1answer
19 views

How to apply scripting only to rows with data in them?

I have a Google Sheet that adds multiples rows per day as forms get entered. What's the best, most hands-off way to apply formulae to rows as they get added? I've built a script, but it applies the ...
0
votes
2answers
32 views

How to group data (by row and column) in a Google spreadsheet?

I have data as follows: Name | Value | Y/N | Value | Y/N A | 10 | Y | 12 | N B | 100 | Y | 120 | N A | 20 | Y | 10 | Y B | 200 | N | 120 | Y C | 1000 |...
1
vote
1answer
24 views

How can I create a unique list of data across multiple rows AND columns?

Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values. This works well for column-specific data, however, you have multiple columns and ...
0
votes
0answers
8 views

How to create a line chart from a key-value dataset?

I have created a script that tracks twitter followers over time, for many accounts, the data is saved to a table that looks something like this: +------------+--------+-------+ | DATE | USER |...
0
votes
1answer
8 views

Can you make a sheet in Google Sheets visible for certain group?

I have a spreadsheet for a timetable where about 5 people work on. I want only the spreadsheet owner can view and edit certain (confidential) data. This confidential data is needed for formula's in ...
2
votes
1answer
25 views

Timestamping purchase entries

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I ...
1
vote
1answer
26 views

Problem combining LEFT() and QUERY() functions

I'm currently working in Google Sheets and I need to import a part of a row from a spreadsheet to another. I'm importing the row with the QUERY() function and "cutting" the data from the row with LEFT(...
0
votes
1answer
18 views

Calculations which won't recalculate

I'm trying to make a simple calculator in Google Sheets with checkboxes for certain values. For simplicity I'll refer to these Checkboxes as A and B. What I would like to have is the user selects A, B,...
0
votes
2answers
41 views

How do I tie an enum to a set of numeric values?

I created a list of items Data Validation in a sheet corresponding to time-frequency: weekly,monthly,annually I want to multiply values in a numeric column with the corresponding weekly values to ...
0
votes
1answer
25 views

Making Google Sheets script work on multiple tabs

Below is my script or code: function myFunction() { } function onEdit() { var s = SpreadsheetApp.getActive().getSheetByName('Sheet1'); s.showRows(1, s.getMaxRows()); s.getRange('H:H') ....
0
votes
2answers
29 views

ArrayFormula for consequential sum-up?

Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/ What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C ...
0
votes
2answers
49 views

IMPORTXML error - empty table

I am trying to import a table from the lower part of the following website to Google Sheets: https://vistacapital.com.br/ I used the formula: =IMPORTXML("https://vistacapital.com.br";"//*[@id='sos-...
1
vote
1answer
11 views

Sum using two filter variables

So I have a ledger sheet that was set up to record costs for a single project. We've added a project column so we can track more than one project so every entry (row) now is identified per project. I'...
0
votes
1answer
31 views

Running formatting script across multiple Google Sheets tabs

I have an onEdit script I'm using to protect/force formatting on a single tab within a google sheets document - and it works perfectly. The issue I have is that I'd like the same script to work on ...
1
vote
0answers
6 views

How can I use Google Apps script when advanced protection is on? [closed]

I recently turned on advanced protection on my Google account. But today I found out that my scripts embedded in Google Sheets don't run anymore, because they are denied access (Error 400). Does ...
1
vote
1answer
17 views

AVERAGEIFS parse error

=AVERAGEIFS(Sheet12!R:R,Sheet12!A:A,N5,Sheet12!B:B,">=9/16/2018",Sheet12!B:B,"<=10/3/2018") While giving cell range instead of a date value, it's showing error.
1
vote
1answer
25 views

How export a spreadsheet as XML?

How can I export a simple spreadsheet, consisting of a single column of data, as XML? Exporting as CSV is leading to other problems, so turning to XML as a workaround.
0
votes
1answer
21 views

Autofill a cell when dates are entered in 3 other cells

I'm trying to create a way to keep track of our jobs by creating a Google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...
0
votes
1answer
25 views

How to make date automatically display only day number and suffix (1st, 2nd, etc.)?

Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell example:1/05/2019 -> 5th I have ...
1
vote
1answer
15 views

Custom formatting with data returns what the cell shows but not what the cell contains

Basically I have this set up in one of my sheets in which these cells have data validation for "is valid date" so a calendar shows up when you double click them, then it has custom formatting so ...
1
vote
1answer
23 views

How do I use the multiply line results from query or filter to get other values from an unsorted sheet?

I'm trying to take the results of a filter with multiple lines and use that results to acquire info from another sheet. Specifically, I'm trying to acquire Quantities in Customer Order sheet using ...
0
votes
1answer
18 views

Create sequential dates with each date repeated multiple times

For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently? e.g. 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/...
1
vote
1answer
21 views

How to include multiple IF statements with different data validations

I'm currently stuck trying to script a formula to populate a total value cell the factors in two different data validation columns (below): The first column is to deduct a certain value per hour, the ...
1
vote
1answer
30 views

Combining 3x IMPORTRANGE with dynamic range

I have 3 Google Sheet and want to combine all of them with: Sheet 1: 480 rows Sheet 2: 40 rows Sheet 3: 20 rows The sheet keeps expanding by the day since the sheet's data comes from a Google Form ...
0
votes
3answers
35 views

Select last entry from spreedsheet

So I have a Google Form dumping data into spreadsheet tab form responses 1. I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
1
vote
1answer
63 views

How to view full images on hover in Google Sheets?

Given a URL to an image, how can I easily insert images into cells into Google Sheets and then have the full images viewable on hover? I'll be doing this on the fly with many images throughout the ...
1
vote
1answer
14 views

How to insert cell text from one cell into an ImportRange Function

Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
0
votes
0answers
40 views

Regex Matching for Google Sheets Query Formulas

I'm trying to return a limited list of cells using a query that references another tab in my Google Sheets book. Please find a sample book here. The returned cells represent social media posts -- I'm ...
0
votes
1answer
68 views

Split multi-line (comma-separated) cell into new rows + duplicate surrounding row entries

Based on: Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) How can I rewrite this for use of a comma as separator and remove blank spaces? Input sheet: ...
0
votes
1answer
38 views

Google Sheets IMPORTHTML parse error

I want to use the IMPORTHTML function of Google Spreadsheets. There is this example by Google itself: =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4) Unfortunately (like ...
0
votes
2answers
41 views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
1
vote
1answer
30 views

Custom Script in Pivot Table Value Formula = Error Loading Data forever

I am very new to the Google Sheets platform, but I am not new to programming, and am comfortable creating and managing javascript code, and reading API documentation. I am working on a side project, ...
1
vote
1answer
48 views

Find value of last non blank cell in a row (while skipping specific columns)

So basically I have a list of data in a row. They are totaled weekly statistics numbers and at the end of every month, there is a "total" column. The column where the 8 is at is where this formula ...
1
vote
1answer
9 views

Merge variable rows into one cell

I have the following data and I would like to have it organized by individual parts while merging the parts IDs into a cell is there any way of doing this with Google Sheets?
1
vote
1answer
36 views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
0
votes
1answer
13 views

Can I move my filter beyond the first row? [closed]

I need to filter a spreadsheet but don't want my filter to be present on the first row as it's including the filter headings within the sort. Is there a way to filter a sheet but exclude the top 3 ...
2
votes
1answer
24 views

Find cell that contains some phrase

I'm looking for replacement of Excel formula: =if.error(lookup(1000;search($B$1:$B$2;A1);$B$1:$B$2); "NONE") The sheet looks like this: So in column A, I have a long product index, in column B, I ...
1
vote
1answer
20 views

If cell contains then

I need to create a formula that will check for a word in a cell and return a value or if the word does not exist then check for another word. Eg. Check Cell A1 contains the word "red" then print "...
0
votes
0answers
17 views

Conditional formatting weekly, fortnightly totals for my budget spreadsheet

I am making a budget spreadsheet. I would like to enter certain expenses periods, i.e. weekly, fortnightly, monthly, quarterly and yearly. I would also like a column showing total monthly and yearly ...
2
votes
1answer
31 views

How to autofill value on Google Sheets

I have the following example here. I have a random value that is no spaced exactly, (some have 10 empty between them other 30). I would like to have some value equally added in the empty spaces. I ...
0
votes
1answer
24 views

How to use calculated fields to pull up “Grand Total” in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
0
votes
4answers
81 views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
0
votes
1answer
17 views

Google Sheets Using Countifs & Unique Together

I am trying count the number of unique clients of Payroll service who are Won. The answer as per the table below should be 4. How can I achieve this, given that the table will be dynamic?