Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[flabort]

I'm redesigning the spreadsheet I use to keep track of who's who, and who's scummy, in the game of mafia here on Bay12. The original sheet was quite simple; version 2.0 is considerably more complicated.
The following link will allow you to view and comment on the spreadsheet; one tab is open to viewer editing, at least the name, time, and opinion columns, so that you can see how it works.
https://docs.google.com/spreadsheets/d/1153EEoMwuRp7yop0KzVK128Mvt5m3SATgEkytmQ5xM4/edit?usp=sharing

I'm trying to figure out how to use the command IMPORTXML:
https://support.google.com/docs/answer/3093342

To figure out how to extract, from the website in cell $H$1, (So so far I have the code "=IMPORTXML($H$1, )"), AKA from a thread of mafia on bay12, the timestamp for each post. I can use the concatenate command to put together the second half of the Importxml command to reference the proper cells, but what is the correct path to find the time stamp? And where in this path do I put the post number?

[flabort]

This is what I have, but obviously the words Post, ID, and Time are wrong:

=IMPORTXML($H$1, Concat("//post[@id='", Concat(C3, "']//time")))

What are the right words to make this work?

[flabort]

I got a result!
Not the result I wanted, but a result!

An IRL friend (OK, my dad) told me how to view a page's source code. From there, I devised this:

=IMPORTXML($H$1,"//div[@class='postarea']//div[@class='keyinfo']/div[@class='smalltext']/strong[last()]")

It doesn't output the right section yet, but it recognizes near the area I'm looking for.

[dorf]

I've modified your spreadsheet (see F19).
It is now extracting the full date and time.

[flabort]

Thank you. Thank you so much.
Of course, I thought I had set that specific sheet to protected (I expected you to use the third sheet), but thank you.

Now I need to figure out how to get it to view multiple pages (I think I can do that by modifying the URL with concat), and only one result per thing, which I can do with the Choose command. Thanks

[flabort]

Hmm. Choose is treating the Importxml formula as one choice.
The multipage change to the formula is working.

[dorf]

Now I can't edit any sheets at all

Leave the multipage formula in the 1st row, clear the rest of the column. You should see 15 timestamps loaded.
Now select the 15 cells and doubleclick the autoexpander at the bottom-right corner of the last-selected cell. It looks like a light blue rectangle.

At this point the formula will copy itself every 15th row and you should be set to go!

[flabort]

Hmm. That might work, it might not.
Because the First Post # field. When entered, then which rows the formula would need to be in would almost positively change.
So I need to figure out how to pick a result from this list. Maybe I need another column.

And gah, I can't figure out why the permissions is acting this way. Maybe they'll behave now.

[dorf]

I'm afraid I don't understand.
If the Post # field will change, simply make another column which will hold the static sequence of numbers needed for IMPORTXML to work.
Once the batches of 15 posts are loaded, you can pick the desired post afterwards, no?

Permissions still don't allow me to edit, but don't worry about it. I'm testing on my own copy.