Pasting in from google sheets

I am needing to paste in from google sheets several sets of cells and their formating including what is a dark border and what is not. I seem unable to find a way to paste in a table. Am I missing something or is this just something that does not work?

I have tried searching but I can’t find someone with this question.

Hello JosephRoberson.

Using Chrome, I find that pasting from google sheets directly into Scriv mostly works, including “text” formatting, with the exception of cell borders.

If you first paste cells from google sheets into excel, and then copy the cells again from excel, and paste those into Scriv, you get the cell borders.

Note though that spreadsheet formulas are not copied along with the cells, only the cell values.

Hope this helps. Welcome to the Scrivener forum!

Thank you! I guess I need to add Excel to my PC now. :slight_smile: At least there is a way to move things over so that will help.

So, I found that I could paste what I wanted to from OfficeSuit Sheets however, it seems that after I reopen Scrivner the table formating of what I pasted in is gone. Any ideas on how to keep the right formatting?

This only happened after you reopened, and before that, all was good?

How much of the formatting is gone? Is the data still in cells, but the style formatting is not as expected; or has all the info from the cells been left as undifferentiated text, or a list?

Yeah that was the odd thing. I pasted it in and my table stayed with the widths I had in the spreadsheet. My merged cells were all right and everything. Then when I closed and reopened several of my cells became more narrow so that the letters were stacked and my merged cells grew which made the ones above them grow as well. I was able to go in and adjust the table using the table options in scrivener and then it took but that kind of defeated a large amount of the reason of pasting in.

I suspected it was something like that. I’ve never used tables in Scrivener, I don’t think. But what you described brought to mind something I’ve seen with lists in Scrivener. And pasting between Scrivener and Google Sheets, and Docs and Gmail too, has formatting issues, maybe even more than between Scrivener and Word, which is somewhat improved since Scriv Win 3.

I believe I’ve confirmed what you’ve found.

Just now, I pasted a small range of cells directly from Excel into Scrivener, and it looked as expected, including cell borders and some bold formatting (all else default in Excel.)

I then pasted those same cells from Excel into a new google sheet, and it too looked as expected.

Then, I copied again the cells I just pasted into the google sheet, and pasted them into a new Scriv doc, and they pasted, including some bold text, but without visible cell borders.

Then I closed the Scrivener project, and then reopened it, and the tables were broken, as expected and as you described, just a jumble of misproportioned cells and squeezed text. The one from Excel still had gridlines, the one from google sheets still did not.

I didn’t check to see if the tables were adjustable, but they’d still need to survive being closed and reopened, again and again. You’d also want to make sure they could survive even a window resize. Lists in Scrivener are fragile, too. I’ve learned to avoid them, even within Scriv, without cross pasting.

Yeah I am guessing that I am going to have to revamp what I am doing to make the project work. I have lots of tables but I might have to do the writing and then add the tables after in a different program before publishing.

I got similar results when testing from LibreOffice Calc and Writer, so it looks to be a problem with how Scrivener pastes spreadsheet data:

  • Calc: initially, the pasted table only has the column width necessary to show the pasted data.
  • Writer: if I first paste from Calc into Writer, and then from Writer into Scrivener, then I get a table with wider cell widths.

Now as for examining the results, you can right-click in the table anywhere and select Table ▸ Table... from the contextual menu, to bring up the table editing palette. With the cell widths for the table pasted straight from a spreadsheet, we get an irrational setting of “1”, and for whatever reason the controls to edit the table cell widths are disabled. Clicking into the table pasted from a word processor, the result is more logical, and the table can be edited.

Reopening the project indicates that when Scrivener is faced with a table that has illogical settings, it sets every column to 46%. My guess is that they only ever tested with two-column spreadsheet setups, and so never noticed that if you have three or six the result isn’t useful. A four column table ends up with a total width of 184%. This is what is causing the weird proportions after reload—but do note you can at that point fix things, so it’s not like the table is broken, it’s just asking the text editor to display it in a way it cannot comply with.

So it seems there are two separate bugs here:

  1. Tables without logical cell width settings should be rebuilt at x / 100, where x is the number columns. Our four column test should result in four 25% width columns.
  2. This kind of correction should also be done for irrational tables that are pasted into the editor. If spreadsheet software routinely does not convey width information then it should be set up with reasonable column widths. However that should be a fallback behaviour, as it seems to me most spreadsheet programs are going to be copying width settings along with the data. When I change a column width in Calc and paste into Writer, I get something that resembles the original.

So we’ll have to take a look at those two (and a half) conditions. In the meanwhile, as noted, it seems that however word processors copy tables is more compatible with what Scrivener expects in terms of column width settings, and so passing the clipboard through a word processor ought to provide a work-around.

1 Like

Maybe it’s just a matter of “taking” the right thing from the clipboard - see this thread

I can see that the default paste (from an Excel copy) is HTML format, and the results is very slightly different to using RTF; my guess is that Scrivener is taking HTML when RTF might work better?

Thanks! Yeah, that makes good sense, most clipboards have a variety of formats to choose from. They may be using HTML pastes to get around other issues though (like too much RTF formatting that isn’t supported).