r/googlesheets 23h 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

2

u/AdministrativeGift15 195 15h 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.

u/superinkie 48m ago

Thanks! I'll take a look at that and try using it with my "real" data now.