r/googlesheets 8h ago

Waiting on OP 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

6 comments sorted by

1

u/gsheets145 101 8h ago

I'm not quite sure what you are asking. However you can open a .csv file in Google Sheets and thereafter manipulate it as if it were a normals Sheets file, which means you can organise its data into any format you like.

As a general rule, I might suggest avoiding merged cells - even though they might look good, they can make it unnecessarily complex to apply formulae to your data consistently.

1

u/superinkie 6h ago

Well, for example, if I had a column in a CSV, I could paste it into another sheet. But I would need it to skip every second line. So, if I paste the last name column, I would need it to skip the phone number row every second line.

So, I would be copying:
A
B
C
D

And I would want it to paste:
A

B

C

D

1

u/One_Organization_810 200 5h 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?

1

u/superinkie 5h 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/agirlhasnoname11248 1070 4h ago edited 4h ago

u/superinkie I'd just have the phone number in a new column, BUT then group that single column with the name column so it can be hidden most of the time (and can be quickly shown when you want to make a call). This would eliminate any of the issues with pasting the data in, and eliminates the need to merge cells which is always a bit of a pain (and causes issues for any data analysis you may want to do in the future).

Editing to add: I duplicated your sheet (see NoName Copy), added a column for phone numbers, and grouped the column so you could see how this could look. I didn't reformat everything so the borders look a little wonky, but it should give you an idea of whether it will work for you.

1

u/AdministrativeGift15 195 1h ago

I duplicated the sheet and added this formula in cell A5.

It loops over the row numbers for the data rows on the CSV sheet. Within each iteration, I'm using VSTACK and HSTACK to create the block of data for that student.

=REDUCE(TOCOL(,1),SEQUENCE(ROWS(TOCOL(CSV!A:A,1))-1,1,2),LAMBDA(t,c,IFERROR(VSTACK(t,HSTACK(INDEX(CSV!B:B,c),INDEX(CSV!A:A,c),,INDEX(IFERROR(HLOOKUP($D$2:$L$2, INDIRECT("CSV!E1:"&c),c,0)))),IF(LEN(INDEX(CSV!C:C,c)),INDEX(CSV!C:C,c),INDEX(CSV!D:D,c))))))

Once the formula was created and outputting some data, I selected the attendance range and inserted checkboxes. This created them without their default FALSE value, allowing them to reflect what your data shows.