1

I'd like to transfer this Excel spreadsheet to Google Sheets, maintaining the automatic background formatting functionality, particularly in the case of the borders.

Here is the same spreadsheet converted to a Google Sheet.

Excel applies the borders through conditional formatting, and they are somehow maintained in the transfer to the Google Sheets format, slightly broken.

You can see how it's meant to function by opening it in Excel, but to describe the intended functionality: If you enter text into a cell in Column B, a number is added in the cell to the left (works correctly) and a top border is applied to the cell through Column R. In Google Sheets, the border is applied two rows down instead.

How do I fix this?

The conditional formatting rule working in the background here is: enter image description here Perhaps there is a clever way to edit this rule in Excel in order to make it translate properly to Google Sheets in the background?

I've tried changing the rule to =ISNUMBER($A$1), =ISNUMBER(A1), and Applies to: $B1:$R1, among other things. These only break the functionality further though.

Improve this question
4
  • Related 1, 2
    – Rubén - Volunteer Moderator - ♦
    Commented Aug 25, 2021 at 22:39
  • I did mention that I tried entering text in the "Cue Title" column, and the borders are applied two rows down from where they're supposed to appear. And you're right, I'm aware Google Sheets doesn't include borders in conditional formatting (in fact I'm the one who asked one of those other posts you've linked :-)), but as I mentioned, they are indeed automatically appearing within Google Sheets somehow, just slightly incorrectly. I've just added a link to the spreadsheet within Google Sheets to make things easier.
    – gills
    Commented Aug 25, 2021 at 22:55
  • By things tried we refer to attempts done to solve the problem :)
    – Rubén - Volunteer Moderator - ♦
    Commented Aug 25, 2021 at 23:01
  • 1
    Roger that, just added that information. There isn't much I know to try in this situation, I'm just stuck, but I did my best to describe it.
    – gills
    Commented Aug 25, 2021 at 23:10

2 Answers 2

Reset to default
1

With @Rubén's insight into how Google Sheets was inheriting border-applying conditional formatting rules from Excel (without a way to access or edit those rules), I was able to figure out a different conditional formatting rule in the original Excel document that achieves the same goal and translates properly to Google Sheets.

enter image description here

For what it's worth, this is a very clever work-around to get conditionally formatted borders into Google Sheets, by creating the rules in Excel first and converting to Google Sheets. It's just unfortunate that it is impossible to edit the rule once it's in Google Sheets.

Improve this answer
0

At this time Google Sheets is not able to reliably convert all the Excel files into Google Sheets format by one hand because Google Sheets hasn't all the features that Excel has by the other because there are some features on converted files that Google Sheets hasn't an user interface nor the Google Sheets API has a resource able to access them.

By the way there are some features that doesn't work the same way.

Considering the above, IMHO, on certain cases the best is to create a Google Sheets spreadsheet from scratch.

Regarding how to apply borders based on cell content, instead of using the built-in conditional formatting feature use an on edit / on change trigger from Google Apps Script to apply the borders.

Disclaimer: I didn't reviewed your Excel file neither the Google Sheets linked spreadsheet, so I can't tell what could be the best approach in your specific case.

Related

Converting Excel files into Google Sheets and Excel Vs. Google Sheets features

Applying borders automatically in Google Sheets

Improve this answer
6
  • I'm confident there is a way to adjust some code somewhere in Google Sheets to make this work, as it is already basically doing what I want, adding those borders. Coming from your perspective, if you were to add borders in this way by creating a Google Sheet from scratch, what would be the appropriate way to code this? Or, as a shortcut to that answer, more back to the original question, what is the code in my linked Google Sheets document that is causing those automatic borders, and how do I adjust it to make it work as intended?
    – gills
    Commented Aug 25, 2021 at 23:35
  • @etudes I edited my answer
    – Rubén - Volunteer Moderator - ♦
    Commented Aug 25, 2021 at 23:43
  • Thanks Rubén, gave those a read. I'm still completely lost. I tried applying the solution to the last article you linked, but it's way too complicated for me to understand. That's why I asked a new question. This is going to require some new code that I don't know how to write.
    – gills
    Commented Aug 25, 2021 at 23:51
  • @etudes If you need help on applying borders using Google Apps Script I suggest you to post a specific question about it.
    – Rubén - Volunteer Moderator - ♦
    Commented Aug 26, 2021 at 0:04
  • That is exactly what this post is about. If there is a solution to my problem that uses Google Apps Script, that would be an appropriate solution offered. I don't understand the need to keep jumping through all of these hoops to get to the same answer to what is the same problem, just asked a different way. We are at the point of working with a completely native Google Sheet, and you're asking me to completely rewrite my question in a new post.
    – gills
    Commented Aug 26, 2021 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.