{SOLVED} Integromat and Google Sheets

Hey Mary! I’m sorry that you’re having trouble here

Have you tried contacting Google or Integromat about these issues? I’m not seeing what’s happening in relation to Airtable here.

Sorry if I’ve missed this!

I don’t think this is a valid range (in Google’s vernacular). I believe a valid range looks like this:

Big Hero 6: The Series’!A1:A

I think a range requires more than one cell, so if Integromat is looking for a range (i.e., an array), giving it a single cell would likely result in the error message. This is further seen in the following error…

It seems to be saying - Hey! I tried writing into a column (which is an array) and you gave me a cell (which is not an array).

Hi @Bill.French

Thank you for your reply.

I did some further testing and it seems that there might be an issue with the data from the website’s public API.

There are some TV codes/shows that are not active, so the data is not passing through to the GS worksheet.

I am now in the process of finding the data from another source and by using a different Integromat scenario.

Thank you for your help.

Mary K.

Hi @Bill.French

I wanted to update you on this issue.

As it turns out, Google Sheet’s API does not recognize colons nor the pretty apostrophe/single quotes. I just removed/changed them and now it works.

I thought you might be interested in knowing.

Thank you for your help.

I’m pretty sure that’s not the cause of your issues. We use colons and apostrophes with all of Google’s SDKs almost every day and across hundreds of apps.

I think what you’ve encountered is a character encoding issue and removal of such characters simply eliminates the encoding failures.

Hi @Bill.French

Interesting, so why would there be an encoding issue?

I was using an iPad. Is it the keyboard?

You got me curious about this.

Mary K

Character encoding issues can arise from a variety of causes, the most common of which is simply copying and pasting content from a browser into another data store. If the source content is not UTF-8 or 16 encoded and the target location unwittingly accepts any encoding you hand it, it’s possible odd character representations will occur. And when that happens, you might not even know it until something needs to convey the copied information through a secondary process such as an integration flow.

How does “cut and paste” affect character encoding and what can go wrong?

First of all, a text editor’s internal representation of text has no bearing on how the text is encoded (serialized) when you save the file. So a document is not “in” an encoding; it’s a sequence of abstract characters. When the document is saved to a file (or transmitted over the network) then it gets encoded.

It’s up to each application to decide what it puts on the clipboard. Typically, a windows app that knows what it’s doing will put a number of different representations on the clipboard. When you paste in the other app, the app will look for the representation that best suits its need.

In your case, a text editor (that knows what it’s doing) will put a Unicode representation of a selected string onto the clipboard (where Unicode, in Windows, is typically moved around as UTF-16, but that’s not important). When you paste in the other app, it will insert that sequence of Unicode characters into the document at the selection point.

Copy/Paste is not the only way to introduce encoding instances. APIs, when used improperly, can result in characters that are not encoded properly. Custom keyboard maps and apps can do this. Poorly-constructed apps (regardless of OS) can do this.

One of the hallmarks of an encoding issue is a tertiary process that removes (or sanitizes bad characters) suddenly causes an app to work as expected, giving you the false sense of comfort knowing that the cause is an underlying platform incapable of dealing with certain characters. Indeed, this is partially true - the platform cannot handle these characters because they are improperly encoded, which is a very different assertion than the platform being unable to handle what you perceive to be a colon or apostrophe.

Without question – setting aside AI and machine-learning – character encoding is the closest computer scientists will ever come to voodoo. It’s complex, it’s difficult to understand, and it is very difficult to debug. But worse, it is the hidden side of computing, a place where perceptions are not typically representations of fact.

I recommend this tutorial on character encoding which will likely create more questions than it will answer. And when that happens, you’ll need this article. :winking_face:

Bottom line - my sense is that the mix of your content process and interchanges of information is causing the issues you have experienced. Deleting characters from a content process and suddenly seeing it work is almost certainly because the characters are non-compliant byte sequences that are being represented to an API that requires UTF or Unicode compliance.