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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

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

How to use vlookup or indirect to copy data from another sheet, without changing the value on update of database

So I've set up automatic MySQL sheet that gets the data from it every day at 8pm. I've set up everything with vlookup, and tried indirect. But I can't figure out how to make sure the values don't ...
0
votes
0answers
17 views

search/match a date on Google Sheets using Google script and copy a source range to a destination range OFFSET from that date

I'm struggling with some simple copy/paste scripting in Google Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets. I have a "Today" data entry sheet. I have a "Program" sheet that ...
0
votes
1answer
11 views

Is it possible to set a timer on Google Sheets ui.alert()?

So I have a ui.alert that triggers and asks for confirmation before making an edit on a Google Sheet. javascript function confirmation(){ //Call Spreadsheet alert and give it a variable ...
20
votes
5answers
73k views

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
2
votes
2answers
19 views

Conditional Formatting Column C when Column B is not empty

I am trying to get column C to turn green when column B has a value in it. I think there is a bug because the custom formula below is not working.
2
votes
3answers
2k views

Is there a way to get 'frozen' rows in an embedded Google Sheet?

I have a 400 row sheet embedded in a web page. I would like the header row to be frozen, as it is in the original sheet. So far I've been unable to discover any parameter to allows this, but also I'...
2
votes
1answer
1k views

How can I fill the diagonal of a matrix with zeros?

I have an empty matrix: I would like to fill the diagonal of a matrix with zeros: Is there any clever way to do it? The matrix can be pretty large and it's tedious to do this manually. I could use ...
0
votes
1answer
19 views

Adding missing dates to Google Sheets

I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function. For ...
1
vote
2answers
141 views

Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
5
votes
2answers
845 views

Fill zeros for the dates when no data was recorded

I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for ...
0
votes
0answers
6 views

sum of specific lines

How can you sum specific lines? When I add up specific lines such as A10:B20:C30 I get the entire column which doubles the figures. It is picking up the specific totals plus the individual columns
0
votes
1answer
15 views

reference cell as sheet in criteria in countif formula

I have a countif formula that gathers information from another sheet. =SUM(COUNTIFS(test!A:A,{"Story","Task"},test!D:D,{"In Progress"})) The sheet is named test. Is there any way I can write test ...
1
vote
3answers
2k views

Google forms script to delete responses counter

I have tried and combined a few pieces of script to delete rows, but this does not reset the counter. Help resetting responses using script would be appreciated. My copy sheet function, and delete ...
4
votes
2answers
4k views

How to reverse the y-axis in a Google Sheets graph?

I have data that represents the depth of an open mine in a transversal view. Currently, it looks like this: But since it represents the depth of a mine, I'd rather have it "flipped". So the y-axis, ...
1
vote
1answer
20 views

How to link a text box in Google Slides to a cell value in Google Sheets?

I have a weekly Slides presentation that summaries result from Sheets. I don't want to have to edit the presentation every week, copying and pasting text from one webapp to another. Since it is ...
0
votes
1answer
18 views

List all sheet names - Google Sheets

I have a Google drive spreadsheet with 20 sheets. Is it possible to list all the sheet names, in an index sheet?
1
vote
1answer
14 views

Syntax for Unicode characters in regexes in Google Sheets?

What's the syntax to use for general Unicode characters in regular expressions in Google Sheets? Use case: When using REGEXREPLACE with user submitted strings in Google Sheets, I have come across a ...
2
votes
1answer
25 views

Dynamically reference data from multiple tabs and sort it

I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity. I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
0
votes
0answers
17 views

Include filename of source spreadsheet

I have 2 spreadsheets for 2 employees and I combine them into 1. From employee 1: From employee 2: I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
1
vote
1answer
15 views

Column of numbers. If one of the numbers is equal to another number in the column, excluding itself, put a “T” in front of it

I'm so close to getting it. I've tried a couple things. My wheelhouse is python. Unfortunately Excel is not my forte. =IF(OR(U2=(U3:U32)),"T"&U2,U2) First attempt. If U2 is in U3:U32, put a T in ...
1
vote
1answer
20 views

Output counted list with extra qualifier

I currently have a table with the following columns: Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
0
votes
1answer
3k views

Barcodes Generator for Google Sheet

Is there a way to generate a barcode based on an input value in a Google Sheet, and displays both the value and an additional title? For example: Value = DPI-001-A02 Title = BB: 1/2/2018 The ...
-1
votes
1answer
11 views

How to replace reference cell and have it affect the main cell if it referencing?

I asked a similar question on the Google Support Forum. I was wondering how I could reference a cell with any sort of value in it to another sheet (inside the same Google Sheets document) and then ...
5
votes
6answers
16k views

How to generate a random string?

I need to create a random string of 8 characters for every row in Google Spreadsheet. Basically string should contain letters and numbers upper and low case only, example: LZJ2cuWT Any idea how to ...
0
votes
0answers
28 views

How do I apply two conditional formatting rules to a single cell?

I have two groups of rules... one color-codes the fill of cells based on one condition for visual grouping of data (7 rules for 7 groups); the other color-codes the text of cells based on another ...
0
votes
1answer
26 views

I need help with calling new data from a Google Form sheet into multiple other sheets automatically

So, I'm setting up something for a friend of mine, and I've been trying to mess around in Google Sheets with getting something to work, but I've run into a bit of a roadblock. To put it simply, I want ...
2
votes
1answer
54 views

How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?

My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx). Currently, I want to automatically convert all Microsoft ...
1
vote
1answer
22 views

Automatically copy first 3 columns from one sheet to each new sheet in Google Sheets

I'm having columns A, B and C filled with data in Sheet1. Those columns contains data that is static for each new sheet. Currently, I need to manually copy the first three columns to each new sheet I ...
1
vote
1answer
19 views

How to combine same value for Google sheet query?

I am trying to create a separate tab in my Google sheet where I can filter the Trainer to see which days they will be conducting a Day 1 and Day 2 Training. Day 1 Training: =iferror( query('...
0
votes
1answer
120 views

How to select existing sheet to be populated by a Google form?

I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report. Now the question is: How can I assign a particular sheet[already created in ...
1
vote
0answers
24 views

Writing a macro to create a new tab and name that tab based on a cell value in Google Sheets

I would like to have a Macro that generates a new tab and names that tab based on a cell value. There is a tab called 'Dashboard'! on which the user enters information in assigned cells. Afterwards, ...
1
vote
2answers
2k views

Cell values ​do not update

I want to update every minute a cell with data from a HTML link. The problem is that the value does not update unless I manually empty the cell or make changes to the script. I tried to add NOW() to ...
1
vote
1answer
53 views

VLOOKUP vs IMPORTHTML

I am having a Google Apps Script issue using VLOOKUP on some data generated by the IMPORTHTML function. For normal user interactions this does not seem to be an issue but scripts accessing the ...
1
vote
1answer
28 views

Synchronizing Cell Changes Across Sheets

I'm trying to connect a pair of cells on different sheets within the same Google Spreadsheet, so that after a new value is entered into one cell, the other cell will update to match that value, going ...
10
votes
3answers
10k views

How can I stop Google Sheets formatting a Forms response as a date?

I have a Google Forms with a question that contains a drop-down box with the following values: - Fewer than 10 - 11-25 - 26-50 - More than 50 If the user selects 11-25, Google Sheets renders the ...
0
votes
1answer
36 views

Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)

Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook). Sheet 1: ...
1
vote
1answer
48 views

Filter the row containing an exact word and highest value in Google Sheets

I have a table (Master sheet) in Google Sheets that looks something like this: https://ethercalc.org/t8f0ze0kyx6a I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
0
votes
1answer
39 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
1
vote
2answers
2k views

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

Google Sheets has several import functions IMPORTDATA IMPORTFEED IMPORTHTML IMPORTXML Sometimes the above functions returns errors like #N/A Imported content is empty and would like to be sure that ...
0
votes
1answer
20 views

Trying to use Google Sheets importHTML() to import a table. But no content appears

I'm attempting to load the following website into Google Sheets: https://www.lasvegasadvisor.com/vegas-lines/?sport=ncaafb&period=0 Can anyone help provide a formula that would work in google ...
1
vote
1answer
19 views

Google Sheets formula for unique values across columns

I'm familiar with the UNIQUE function in Google Sheets and how it works on rows (see Examples 1 & 2 below). Is there a function in Google Sheets that operates similarly on columns? In Example 3 ...
0
votes
0answers
45 views

A replacement for Excel Camera Function [on hold]

I'm getting into Google Spreadsheet after using Excel only for years. While I'm happy with it, there are some parts of Excel I haven't been able to replace. One of those is the Excel Camera function. ...
0
votes
1answer
25 views

Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column

I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below I want to summarise the cost by element, but ...
7
votes
5answers
10k views

In Google Sheets, how does one get a 12-hour time of day *without* an AM/PM indicator?

I am trying to display times in Google Sheets without an AM/PM indicator, but with 12-hour time (not 24-hour "military-style" time). I do not need the AM/PM indicator as it is redundant in my ...
0
votes
1answer
24 views

Can I import data from web store directly to Sheet?

I have a spreadsheet that analyzes data uploaded into a sheet from a .csv file that is downloaded from my store for various operations and reports that I extract from it. I would like to know if ...
0
votes
2answers
1k views

Stop Google Sheets formatting text as URL

When I type "product.name" or "product.id" into a cell, it's auto–formatted as a URL (different colour, underlined, hover shows it as an external link). But it's not a URL, it's plain text. Similar ...
0
votes
3answers
370 views

Remove duplicate items from array?

What would be the best way to remove duplicate items from cells in rows? For instance... Category 1 | Item 1 | Item 2 | Item 2 | Item 3 Category 2 | Item 3 | Item 1 | Item 2 | Item 3 ...
0
votes
1answer
33 views

How to sort Column B to match with column A in Google Sheet?

I have 3 columns in Google Sheet. Column A have 95 rows with value. Column B and C have 500 rows with value: I need to sort Column B to match with Column A. Column C represent value associated with ...
1
vote
1answer
201 views

Automatically add values before and after a value in a Google spreadsheet

I have a library of flags online and would like to have a client-foolproof solution for adding flags. Right now my function looks like this: =image("http://www.website.com/flags/us.png", 3) But I ...
3
votes
2answers
822 views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...