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
1answer
59 views

Insert Blank Row after Every New Value in Google Sheets

I hope someone could help with this one. I’m not sure if this is possible but I would like to know if there is any way to insert a blank row after every change of Data in ColumnA(Country) which is the ...
0
votes
1answer
253 views

Resolve “Can't sync your changes. Please copy your recent edits, then revert your changes.” error in Google Sheets

Every time I delete a row in a certain worksheet in a Google Sheets spreadsheet, I get the following error: Can't sync your changes. Please copy your recent edits, then revert your changes. This ...
0
votes
0answers
33 views

Show the top 1 value using pivot table in Google Sheets

I have a pivot table in Google sheet and would like to filter the top 1 item in the table. What custom formula is needed to achieve this in the filters? Sheet: https://docs.google.com/spreadsheets/d/...
-2
votes
1answer
31 views

Move cell values from one cell to another with scripts

Move cell values from one sheet to another using a button. Consider 3 sheets. One sheet is called "Tentative Attendees." It contains a column of names. Say cell A1 contains "John Doe,&...
0
votes
1answer
18 views

ERROR IN QUERY FORMULA GOOGLE SHEETS

I need to join two worksheets from different files in another one that already exist in Google Sheets I tried to do this: =QUERY({IMPORTRANGE("1GoKFiJjBKcGQGGi6JMvk9xD5TIypsNSmpLCTeadWQnU",&...
-1
votes
1answer
23 views

Google sheets: Create new row when row is filled

I need to create a log of sorts and would like it if i could have an empty row (Row 2) and when something is entered in row 2 it adds a new empty row above so the newest entry is always at row 3, is ...
0
votes
1answer
10 views

Maintaining absolute references when adding new rows to a sheet?

When I add a new row for the current reporting month to the top (becomes row 9), my absolute cell references in my formulas on rows 3–5 change to move down a row, now referencing rows 10 and 11, ...
0
votes
0answers
11 views

Protect or Lock Particular Columns Formatting in Google Sheet

I have a file where we want to lock or protect multiple columns formatting to "PLAIN TEXT" . So if users copy and paste or paste as values should not alter the formats?
1
vote
3answers
36 views

Average of every 3 rows

Say I have a sheet like this: A B 1 32 22 2 25 44 3 10 58 4 14 5 87 6 32 7 5 8 97 9 73 The sheet in reality has hundreds of rows. I need a formula that averages the ...
-1
votes
1answer
13 views

Parse Error on Google Sheet pull

I’ve setup a google form to autopopulate a google sheet, and pln to use the google sheet to autopopulate other google sheets. I’ve been able to populate other tabs within the same sheet with the ...
0
votes
1answer
29 views

Filter the data of two different column

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JIRA which consists of the following columns: Issue Type Key Status Priority Summary ...
0
votes
0answers
16 views

Extracting PDF data directly into Google Sheets

It blows my mind that I haven't seen anyone else with this issue yet, but here is the situation: I'm looking for the best method to convert a PDF into a Google Sheet (or how to scrape the data). I've ...
1
vote
1answer
22 views

How can I add 1 to numbers in specific cells in Google Sheets?

I have some cells that just have plain integer values and would like a way to add 1 to them all without having to manually do it in each cell. Sorry if this is really simple; I have little to no ...
0
votes
1answer
20 views

How to make time series chart, where all rows are labelled and dates are not unique?

Say I have some data like so | Month | Count | Type | | | |-------|-------|------|--- |--- | | 1 | 2 | A | | | | 1 | 3 | B | | | | 2 | 1 | A | | ...
2
votes
0answers
22 views

Is there a way to get multiple share links from Google Drive in order?

I have 100s of files on my Google Drive where the files are sorted by name. All I want is the share link of each file in the order they appear in Google Drive. I tried selecting all these files inside ...
1
vote
1answer
54 views

Google Sheets: aggregate and extrapolate irregular data over time

I have a list in which I now-and-then (no fixed interval) enter the available budget. Most of all I want to extrapolate this to a future trend of the coming 10 years. (no rocket science, more of an ...
0
votes
1answer
19 views

Trying to make duplicates with permissions

I am currently working on trying to make duplicates with permissions using google spreadsheet. There was a link that I saw that explained how to approach this(Google Sheet Duplicate with Permission). ...
0
votes
1answer
28 views

Export Multiple Google Calendars to Google Sheets

I have several Google Calendars I'm subscribed to, one for each of my sales and logistics team members. I want to grab the events from each calendar for both today and tomorrow and put them into my ...
1
vote
0answers
22 views

Google Sheets: How do I protect a sheet without locking its sheet name?

I have a Google Sheets workbook in which I need to protect all cells for data purposes. However, I need restricted users to be able to change the tab title of the individual sheets’ names. Does anyone ...
1
vote
1answer
34 views

Google Sheets: filtering and matching a list treating NON-unique values

I have a list (column A) of non-unique text values. Items from that list are put into column B (via dropdown data validation). Any items from column A that are missing in column B are shown in column ...
0
votes
1answer
35 views

Find transactions from new paypal addresses

Demo sheet - https://docs.google.com/spreadsheets/d/1H78_Y7H_KOe1HKEKAdqcucUYV1NjsQBstUERzvURJcs/edit?usp=sharing This is used in an attempt to reconcile paypal payments from different domains - quite ...
1
vote
2answers
22 views

Want to use a cell to get a limit number

Here is my query. I want "select * limit 5") the 5 to come from a cell. How do I do that? =ArrayFormula(AVERAGE(QUERY(SORT(Tabell!D4:D33,ROW(Tabell!D4:D33)*ISNUMBER(Tabell!D4:D33),0),"...
1
vote
1answer
46 views

In Google Sheets, find most recent comment for a domain

Using this sheet - https://docs.google.com/spreadsheets/d/1SFXBBlgX6KoffGqQ3HHsRmFcDurGiUjFLYXT3igy8Nk/edit?usp=sharing On the Demo sheet, please help me display the most Recent Comment from the Log ...
0
votes
1answer
20 views

Date-Sensitive Interpolation of Missing Data in Google Sheets

I am trying to calculate the correlation coefficient of a dataset with missing data. It's difficult to describe why data is missing, so I posted an editable spreadsheet to help illustrate the problem: ...
0
votes
1answer
16 views

Freezing in Google Sheets

Is there a way to freeze columns in this manner in Google Sheets: Freeze A-C, then As you scroll past F, freeze it, then As you scroll past I, freeze it too? To help understand why I need this, ...
0
votes
1answer
20 views

Conditional formatting to highlight values that are not in valid date format

I have a Google Sheets spreadsheet with a date column. A contributor to this spreadsheet keeps entering dates in invalid format. I would like to conditionally format the cells in this column to have a ...
1
vote
1answer
21 views

G Sheet - Sumifs for data from another sheet

I have a sheet with bank statements and a want to set up a summary sheet with the total amounts per client. I know how to do that from within the statements sheet, e.g. summing up client A: =SUMIF(B1:...
0
votes
1answer
10 views

I'm having trouble getting this to export military time

Can you tell me how to modify the date/time format to give me military time? I'm Los Angeles time which is GMT-08:00 It's giving me 07:28 but what I want is 19:28.
0
votes
0answers
15 views

Creating a Dynamic Validation List based on Values in Corresponding Row

I'm looking to have a unique Data Validation list for every cell in a single column. The list would be dynamic, only pulling the correct categories where values (I used an "X" in my example)...
0
votes
2answers
45 views

Restructure (flatten) data of Google Sheets table in a new table

Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is ...
0
votes
1answer
36 views

Calculate cumulative returns on Google Sheet with blank cells

The formula that is in the Cumulative Return cell that is showing -0.09% is this. =IF(ISBLANK(J2), 1, (1+J2))*IF(ISBLANK(J3), 1, (1+J3))*IF(ISBLANK(J4), 1, (1+J4))-1 How can I make it so that this ...
1
vote
1answer
60 views

Sheets - Lookup most recent entry based on unsorted date value

Spreadsheet Im working on: https://docs.google.com/spreadsheets/d/1rTzdmKVMqD8sz1D639y5qgvXNBcWaAuBaQOAmwJXdA4/edit#gid=0 I need a formula that returns the Comment for the Domain that equals the ...
0
votes
2answers
12 views

How do you use the SPLIT function to separate unseparated characters in Google Sheets?

I have tried the function SPLIT with the argument "" and it does not work. I have a list that is phylogenetic code, so in the source file it's structured like: 10??12103??? etc., and I want ...
0
votes
0answers
36 views

Google Sheets equivalent of Excel's single accounting and double accounting underline styles without spacer columns

In Excel, if you go to Format > Cells… > Font > Underline, you can select from the Underline pulldown menu Single Accounting and Double Accounting underline styles, which, among other things, ...
0
votes
1answer
25 views

A Google Sheets format that returns time durations in days, hours, and minutes

I have a formula in a cell that returns the duration of a certain task in days. I'd like to express it in terms of days, hours, and minutes. (For instance, 3.25 might be written like 3 6:00.) I ...
1
vote
0answers
23 views

Google Sheet Script with Multiple tabs [closed]

I am using the google sheet script below to do Start and End Time on my sheet and it is working fine. I need to do it on another sheet (tab) with the same function not affecting the 1st tab. Can ...
1
vote
2answers
30 views

IF Statement works in Excel but not G Sheet?

I have a simple IF statement in Google Sheets that is not computing correctly but it is computing correctly in Excel. The formula is =IF(G15="TRUE",1,0) and G15 is a cell with "TRUE&...
-1
votes
1answer
49 views

How do I get Google Sheets to automatically subtract a value from one cell by entering the value in another?

I have a budgeting spreadsheet on Google Sheets which I’d like to be able to do two things (the first, I think, is easier than the second and the first is the priority for me): I write a value in, ...
1
vote
0answers
14 views

Getting web data into Google Sheets - from formatted chart [duplicate]

Edited: this question was associated with another question. I read that answer, appreciate it. Since importxml not working, I wonder what the format would be to read this in another way like ...
0
votes
1answer
52 views

How do I use non capturing groups in google sheets?

This is what I intend to do. But a similar one doesn't work. As with any problem I post, I still can't solve it after more than two hours of trying. :-( https://github.com/google/re2/wiki/Syntax
0
votes
0answers
17 views

2 Google Forms linked into 1 Googlesheets

I have 1 googlesheets file with 2 sheets in it and both sheet has different forms. I have a code that calls 2 functions format and prio: function onFormSubmit(e) { Format(e); Prio(); QUESTION: how ...
0
votes
1answer
21 views

How to filter out empty cells in Google Sheets With Query & Importrange

I am trying to use this formula but it's not working. Do you hava an idea why? QUERY(IMPORTRANGE("IMPORTEDDATASHEET";"06_20!M:AJ");"SELECT SUM(Col17) WHERE Col1 = 'Company ...
1
vote
2answers
47 views

How to split the results of formulas in one cell over multiple columns in Google Spreadsheets?

Below is actually everything that is needed to answer this question.
1
vote
1answer
23 views

How do I conditional format a table in Google Sheets based on two other cell values?

In the scenario below, the table contains random integer values. I want to use conditional formatting in order to highlight the values in the table between the specified min and max values. While I ...
1
vote
2answers
46 views

Extracting text from a capture group while using an arrayformula to consider multiple criteria

I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, ...
0
votes
1answer
26 views

Collate Data in Google Sheets

I am trying to collate date in Google Sheets. I have multiple tabs and want to consolidate them into one tab where each row is from a different tab. If that doesn't make sense, here is my test ...
0
votes
0answers
17 views

Can't get chart to display weeks from specified day

I can't for the life of me get my chart to display every Monday (starting from 13/07, instead it displays every Sunday (starting with 12/07).The chart also starts with 6 data points (minor lines) and ...
0
votes
1answer
34 views

Transposing values in multiple vertically aligned tables into a single table

I have the Fruit Count tables on the left. If at all possible, what formulae would dynamically provide the values in the highlighted F4:G6 range without script editor? Here is the live spreadsheet ...
0
votes
1answer
42 views

Get List of *new* Google Drive Files within Specific Folder (including subfolders) into Google Sheet

I've seen some helpful answers to this question, but none that have the script specify to list only new files/folders added to the parent folder listed. I think what I'm trying to do is combine this ...
0
votes
1answer
21 views

Paste values +transposed

IS there a way to paste transposed but also just values? I want to paste values only, not formulas, keep the destination format and also transpose the data pasted. Is there a way to do this?