Tag: Sheets

google sheets – Query or filter the data when using date time format

Googlesheet: https://docs.google.com/spreadsheets/d/1hO_KatnkP58wuF6I7kwtlGgCI6xM6ubopgCwuYMXXIU/edit?usp=sharing

Sheet2

In cell A2 I have:
=QUERY(Sheet1!A1:Q, “Select * where B >= datetime ‘”&TEXT($A$1,”MM-DD-YYYY HH:mm:ss”)&”‘”)

In cell A1 I have:
6/17/2020 10:53:22 AM

Sheet1:
Column A (user) and B (date and time)
User1 6/17/2020 10:53:22 AM
User2 6/17/2020 10:53:22 AM
User3 6/17/2020 11:10:55 AM
User4 6/17/2020 11:33:57 AM
User5 6/24/2020 10:47:34 AM
User6 6/25/2020 4:50:39 AM
User7 6/24/2020 4:00:24 AM
User8 7/4/2020 5:44:25 AM
User9 7/4/2020 7:08:29 AM
User10 7/4/2020 8:49:24 AM
User11 7/3/2020 7:36:46 AM

The expected output in A2 Sheet2 is:
User1 6/17/2020 10:53:22 AM
User2 6/17/2020 10:53:22 AM

but the query I used outputs all of the data from Sheet 1.

Hope someone can help me…

importrange – importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error

={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:P")<>"");FILTER(IMPORTRANGE("1tWboZO37-_V5nJTNNxRhlvCIONLwHU43iie67zg4IU4", "Trade and Manufacture!P17:T"), IMPORTRANGE("1tWboZO37-_V5nJTNNxRhlvCIONLwHU43iie67zg4IU4", "Trade and Manufacture!P17:P")<>"")}

I am using Google sheets as a management system for a game I play in my classroom with my students. I was able to make a dynamic trading table – as in the students can post a trade good for sale in their workbook, and then it populates to this range (in a separate book) and then is sent to every student’s workbook.

This line is just for me testing if it worked across two workbooks, and it will be expanded to more of them – so this function will get larger. The problem I am having is that during testing with items listed – formula works great. The moment either of the two testing workbooks has NOTHING in the trade offers – it breaks. “Array Literal was missing values in one or more rows” I want it to ignore that and just post what is has available, and not break if even one country is not selling something.

Caveats
This code will be expanded to pull from more workbooks, so please note what has to be repeated vs what just applies to the single line of code.

Yes I know I should probably learn a coding language to make this work better (if you saw the rest of this workbook….. ) but I am a teacher and spare learning time is at a premium right now.

I do have an active script to make a dynamic drop down in another part of the workbook, so if a new script is needed I am not opposed to it – I just won’t be able to do it from scratch.

Multipliers In Google Sheets – Web Applications Stack Exchange

I’m trying to apply multipliers to rows within a column total, having issues..
I’ve tried searching and have made multiple attempts using ARRAYFORMULA, MULTIPLY, SUM, and combinations of these, with no luck..

I have a table that looks like this:

Totals      =            =        5 =        1
==============================================
Multiplier  =  Row Label = Column 1 = Column 2
==============================================
         0  =            =        1          0
         1  =            =        1          1
         2  =            =        2          0

As you can see in the example, I’ve applied the multipliers to demonstrate what I’m looking for.
What I’m looking for is for the “Totals” Row to reflect the “Multiplier” associated with the given column and row..

If possible, I’m trying to do this without applying any custom functions..

TIA for any help..

google sheets – How do I convert rows with 45 columns into 15 separate rows, each with 3 columns?

I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns:

B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ.

I’d like to have it output as:

B1 | C1 |D1

D1 | E1 |F1
....

AO1|AP1|AQ1

B2 | C2|D2
....

Is this possible?
I’ve tried using this:

=FILTER({Sheet1!B:B,Sheet1!C:C,Sheet1!D:D;Sheet1!E:E,Sheet1!F:F,Sheet1!G:G;Sheet1!H:H,Sheet1!I:I,Sheet1!J:J;Sheet1!K:K,Sheet1!L:L,Sheet1!M:M;Sheet1!N:N,Sheet1!O:O,Sheet1!P:P;Sheet1!Q:Q,Sheet1!R:R,Sheet1!S:S;Sheet1!T:T,Sheet1!U:U,Sheet1!V:V;Sheet1!W:W,Sheet1!X:X,Sheet1!Y:Y;Sheet1!Z:Z,Sheet1!AA:AA,Sheet1!AB:AB;Sheet1!AC:AC,Sheet1!AD:AD,Sheet1!AE:AE;Sheet1!AF:AF,Sheet1!AG:AG,Sheet1!AH:AH;Sheet1!AI:AI,Sheet1!AJ:AJ,Sheet1!AK:AK;Sheet1!AL:AL,Sheet1!AM:AM,Sheet1!AN:AN;Sheet1!AO:AO,Sheet1!AP:AP,Sheet1!AQ:AQ},LEN(Sheet1!A:A,Sheet1!A:A,Sheet1!A:A))

But it was just based on another answer I saw on here, and I am sure that I am not applying the filter(range,len()) part correctly.

Google sheets: How do I convert rows with 45 columns into 15 separate rows, each with 3 columns?

I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns:
B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ.

I’d like to have it output as:

B1 | C1 |D1

D1 | E1 |F1
….

AO1|AP1|AQ1

B2 | C2|D2
….

Is this possible?
I’ve tried using this:
=FILTER({Sheet1!B:B,Sheet1!C:C,Sheet1!D:D;Sheet1!E:E,Sheet1!F:F,Sheet1!G:G;Sheet1!H:H,Sheet1!I:I,Sheet1!J:J;Sheet1!K:K,Sheet1!L:L,Sheet1!M:M;Sheet1!N:N,Sheet1!O:O,Sheet1!P:P;Sheet1!Q:Q,Sheet1!R:R,Sheet1!S:S;Sheet1!T:T,Sheet1!U:U,Sheet1!V:V;Sheet1!W:W,Sheet1!X:X,Sheet1!Y:Y;Sheet1!Z:Z,Sheet1!AA:AA,Sheet1!AB:AB;Sheet1!AC:AC,Sheet1!AD:AD,Sheet1!AE:AE;Sheet1!AF:AF,Sheet1!AG:AG,Sheet1!AH:AH;Sheet1!AI:AI,Sheet1!AJ:AJ,Sheet1!AK:AK;Sheet1!AL:AL,Sheet1!AM:AM,Sheet1!AN:AN;Sheet1!AO:AO,Sheet1!AP:AP,Sheet1!AQ:AQ},LEN(Sheet1!A:A,Sheet1!A:A,Sheet1!A:A))

But it was just based on another answer I saw on here, and I am sure that I am not applying the filter(range,len()) part correctly.

Any help would be greatly appreciated!

How to make this Google Sheets script dynamic?

I’m working in a script to paste a table on the same sheet, in my code I’m only pasting the table on the row A18

function NewQuote() {
 var ss = SpreadsheetApp.getActive();
  ss.getRange("A18").activate();
 ss.getRange('A1:F14').copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

I want to make this value dynamic and pasting the table on the next available row.

What’s the equivalent of Windows F4 to make a cell reference absolute (e.g. $A1$1) in Google sheets?

What’s the equivalent of Windows F4 to make a cell reference absolute (e.g. $A1$1) in Google sheets?

Online searchers tell me it’s apple (command) + T but this just opens up another tab in Chrome. I guess they mean Excel.

Anyone know the shortcut for this is for Sheets?

synchronization – Howto sync offline sheet from Sheets app

I did some offline work on a google sheet file on my mobile using Sheets app. I actually did not realize that I am offline, as I never set this option, at least not on purpose.

After I connected to the file using laptop browser, and did some extra work. only than I realized I do not see the changes I made on the mobile app. Reopen the app, and the changes were lost as the file was synced with the last edit on the laptop, which did not have these edits.

So two questions:

Is there some sign to indicates “file is offline”?

how to force sync from mobile app, after working offline?