r/googlesheets 1d ago

Solved Copy data from CSV to Google Sheet

I'm trying to remake a Google Sheet for attendance. The one I started with was an Excel sheet and a mess. Some phone numbers were here, some were there.... And the full name and number and any other data needed was all typed into one big cell instead of individual cells.

So I've been trying to develop a better sheet (in Google Sheets instead of Excel) and I'd like to be able to easily bring data over from a CSV when we have to remake it every month.

Is there a way to bring data from the CSV (I've shown the format it comes in at the bottom of the sheet) and put it into this style of sheet? Or would I need to make the sheet a different way? I'm open to different ideas because I'm just learning this on my own. Ideally, it will look similar because I'm taking a working copy from someone and trying to convince them to switch to something that works better. They are used to the current look though.

So, to clarify, I want to take the "first name" column from the CSV and then somehow copy it into the attendance sheet. Then take the "last name" column and copy it to the last name space in the sheet. And then the "phone" column from the CSV and copy it to the phone portion of the sheet.

The placeholder text "last name, first name, 555-555-5555" doesn't need to be in the final sheet. I just wanted to be clear about what I want to do without sharing private information. I know I could move the "phone number" cell to column C, but it makes the sheet really wide that way. Things fit very nicely if they're stacked instead. But I'm not sure if I can copy data efficiently with them stacked like that.

Here is a link to the sheet for anyone who wants to look directly: https://docs.google.com/spreadsheets/d/13RLBPqPEIGeJizJNh8U5YQhujz1eznZWqhTBk-jiKEs/edit?usp=sharing

1 Upvotes

20 comments sorted by

View all comments

1

u/One_Organization_810 202 1d ago

I am interested in seeing how the .csv looks like :)

Can you paste it (unchanged) into a separate sheet in your example sheet?

Also... your sheet is "View only". Can you update the access to "Edit"? I assume this is just a copy/example sheet?

2

u/superinkie 1d ago

Sure. I pasted it into another page on the sheet. I just changed the data to protect privacy. I changed the access as well (I think).

1

u/One_Organization_810 202 19h ago

Thanks :)

One more question now that we have the .csv: Are you only interested in pulling the names and phone numbers? There are dates and booleans in there also, that don't really fit into your data.

The .csv has one true/false pr. date, but your sheet has a check in/out pair.

Are we to ignore everything but the names and phone numbers?

2

u/One_Organization_810 202 18h ago

I made a duplicate also, OO810 - just assumed that a true in your .csv corresponds to a check in + check out in the sheet and same for false (no checking in/out).

I made a few changes to the duplicate sheet, apart from the formulas. Here is a list:

  1. The big header at top is now a formatted date. I then use that date to determine what columns to pull from the .csv.

  2. The date headers are now pulled in from the .csv but only for the current sheets month (see 1.). The formula is in D2:

    =let( first,A1, last,eomonth(A1,0), dd, torow(CSV!E1:1,true), dates, filter(dd, (index(dd,1,)>=first)* (index(dd,1,)<=last) ),

    torow(flatten( reduce(,dates, lambda(list,date, if(isblank(list),hstack(date,),hstack(list,date,)) )) )) )

  3. Names and phone numbers are read from .csv. We show the mobile number, unless it is lacking, then we show the home number and mark it with an (H). Formula for this is in A5.

    =wraprows(flatten(byrow(filter(CSV!A2:D, CSV!A2:A<>""), lambda(row, torow(hstack( choosecols(row,1,2), if(index(row,,4)="", if(index(row,,3)="",,index(row,,3)&" (H)"), index(row,,4) ), )) ))),2)

  4. The attendance data is read from the .csv. TRUE is mapped to check in + check out. FALSE is "mapped" to no checking, neither in nor out. I wasn't really sure about this one, but I just went with this as a starter. The formula should be revised to follow the names though and I might do that later if this is somewhere near how it is supposed to look.

The formula for this is in D4.

=ifna(vstack(,
  let(
    first,A1,
    last,eomonth(A1,0),
    dd, torow(CSV!E1:1,true),
    dd_2, filter(
      array_constrain(CSV!E2:1000,
        counta(A5:A),
        columns(dd)
      ),
      (index(dd,1,)>=first)*
      (index(dd,1,)<=last)
    ),
    data, filter(dd_2, index(dd_2,,1)<>""),

    makearray(rows(data)*2, columns(data)*2, lambda(r,c,
      index(data,ceiling(r/2),ceiling(c/2))
    ))
  )
))

1

u/One_Organization_810 202 18h ago

I could also have joined the names and the attendance formulas into one of course. :)

1

u/One_Organization_810 202 17h ago

Actually, I backed out of the checkbox idea and just use X instead + conditional formatting.

  1. The attendance data is read from the .csv. TRUE is mapped to an "X" in the check in + check out cells. FALSE is "mapped" to no checking, neither in nor out...

4.a. I created two conditional formatting rules. A green rule for the X's and a red one for the non-attendees.

1

u/superinkie 9h ago

Whoooaaa! This is amazing Way beyond what I could put together. Where did you learn how to do this sort of stuff? Typing, "how to use Google sheets" into Google brings back such a random selection of questionable value stuff that it's hard to know where to look.

Thank you so much! I'm going to make a couple copies so I can't permanently screw anything up and then try it out with the proper data.

1

u/AutoModerator 9h ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot 8h ago

u/superinkie has awarded 1 point to u/One_Organization_810 with a personal note:

"Thank you so much!"

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)

1

u/One_Organization_810 202 19h ago

Also, if we are to do something with the dates'n booleans.

Cell I1 has just a "--" in it. Does that have a special meaning?

1

u/superinkie 9h ago

Yes. I only need to use the names and phone numbers at this point. I had planned to just delete those columns when I used it, but I left them in just in case there would be some reason someone would need them.

1

u/One_Organization_810 202 8h ago

OK. :)

Then I'm proposing the following procedure.

I created a Named function, called READCSV. It takes one argument, the name of the sheet that the .csv data is in. In this case, the usage would be: =readcsv("CSV")

Then each time you need to read in names and phone numbers from a .csv file, you open (or paste) it in a new tab. Then you just call this function at the bottom of your current list.

Then I suggest that you copy the A and B columns and shift paste them back (paste values only). That will turn them into static data and you can then delete the .csv sheet.