google-sheets – Page 12

Is it possible to ‘prefill’ a google form using data from a google spreadsheet?

February 16, 2023 by Tarik

You can create a pre-filled form URL from within the Form Editor, as described in the documentation for Drive Forms. You’ll end up with a URL like this, for example: https://docs.google.com/forms/d/–form-id–/viewform?entry.726721210=Mike+Jones&entry.787184751=1975-05-09&entry.1381372492&entry.960923899 buildUrls() In this example, question 1, “Name”, has an ID of 726721210, while question 2, “Birthday” is 787184751. Questions 3 and 4 are blank. … Read more

Is there a Google Sheets formula to put the name of the sheet into a cell?

February 8, 2023 by Tarik

Here is what I found for Google Sheets: To get the current sheet name in Google sheets, the following simple script can help you without entering the name manually, please do as this: Click Tools > Script editor In the opened project window, copy and paste the below script code into the blank Code window, … Read more

How to address the current cell in conditional format custom formula?

February 1, 2023 by Tarik

The current cell is addressed by the first cell of a range in the conditional formatting. In your example, the range is A4:M10 and therefore you can use A4 as “current cell”. Check for empty content: =A4=”” Relative vs absolute references in conditional formatting work just like copying a formula. Check that the cell in … Read more

Google spreadsheet “=QUERY” join() equivalent function?

January 27, 2023 by Tarik

Short answer Google QUERY Language version 0.7 (2016) doesn’t include a JOIN (LEFT JOIN) operator but this could be achieved by using an array formula which result could be used as input for the QUERY function or for other uses. Explanation Array formulas and the array handling features of Google Sheets make possible to make … Read more

How can I copy a conditional formatting from one document to another?

January 27, 2023 by Tarik

If you want to copy conditional formatting to another document you can use the “Copy to…” feature for the worksheet (click the tab with the name of the worksheet at the bottom) and copy the worksheet to the other document. Then you can just copy what you want from that worksheet and right-click select “Paste … Read more

Google Spreadsheet, Count IF contains a string

January 25, 2023 by Tarik

It will likely have been solved by now, but I ran accross this and figured to give my input =COUNTIF(a2:a51;”*iPad*”) The important thing is that separating parameters in google docs is using a ; and not a ,

Selecting Multiple Values from a Dropdown List in Google Spreadsheet

January 24, 2023 by Tarik

To Add to AlexG’s answer, a better and enhanced version of multi-select is found in this following link (which I tried and worked as expected): https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7 For general guidance on setting up a script in Google Sheets, see this quickstart guide. To use this script: In your Google Sheet, set up data validation for a … Read more

Get value from the cell above

January 20, 2023 by Tarik

You can address it like this: =INDIRECT(ADDRESS(ROW()-1,COLUMN())) COLUMN() returns a numeric reference to the current column ROW() returns a numeric reference to the current row. In the example here, subtracting 1 from the row gives you the previous row. This math can be applied to the ROW() and/or the COLUMN(), but in answering your question, … Read more

count distinct values in spreadsheet

January 14, 2023 by Tarik

Link to Working Examples Solution 0 This can be accompished using pivot tables. Solution 1 Use the unique formula to get all the distinct values. Then use countif to get the count of each value. See the working example link at the top to see exactly how this is implemented. Unique Values Count =UNIQUE(A3:A8) =COUNTIF(A3:A8;B3) … Read more

Determining the last row in a single column

January 9, 2023 by Tarik

How about using a JavaScript trick? var Avals = ss.getRange(“A1:A”).getValues(); var Alast = Avals.filter(String).length; I borrowed this idea from this answer. The Array.filter() method is operating on the Avals array, which contains all the cells in column A. By filtering on a native function’s constructor, we get back only non-null elements. This works for a … Read more