Issue with image when uploading products via CSV to Shopify

[Mrak123]

Firstly I appreciate this may be more specifically an issue with Shopify, and I have posted on their forum. But I was hoping someone on here may have gone through the same trouble I am having an have a good solution.

I am trying to find a good method of uploading products to shopify via CSV, but I am having difficulty as the images I have are hosted on Google Drive, which does not in normal cases allow images to uploaded to shopify via CSV. I had this very same issue when working out how to upload products to ebay via CSV, as they also do not allow Drive URL's for the same reason. 

With Ebay, I was able to manipulate the URL to a format that was accepted. I use an AppSheet app to collate all the information needed, take photos of products, and produce a CSV document to upload to ebay. The URL below worked very well for ebay, no issues at all:

https://www.appsheet.com/image/getimageurl?appName=MRAK-2598868-21-02-20-2&tableName=Received&fileNa...

When you click on this link you will notice that the URL redirects you to this address:

https://lh3.googleusercontent.com/_PidX8_pEaOvh1T_jiy3zY-sDg1b_DJDuKVBUFVcw-uMYCoVpJXlcjbEEKvxs2E=w0

Sadly, neither of these two URL's are accepted by Shopify when I try and upload them via CSV. I get the "Validation failed: Image failed to download - Invalid URL"

Now, I have managed to find that by removing "=W0" from the end of the URL, and replacing it with "?raw=1" Shopify will allow me to upload the image via CSV. The only downside, is that for whatever reason, when it is uploaded to shopify the quality of the image is not great, despite the image looking good at its URL:

https://lh3.googleusercontent.com/_PidX8_pEaOvh1T_jiy3zY-sDg1b_DJDuKVBUFVcw-uMYCoVpJXlcjbEEKvxs2E?ra...

If anyone has any advice on why the quality of the image is pants in shopify, or if anyone has experience of working with AppSheet and Shopify in this way that would be amazing. Also open to any other suggestions really.

[Glooglony_Sloth]

Ever find a fix?

Funny I can drag and drop the url from a browser into other websites, but the url text is rejected.

[Mrak123]

Yeah I wouldn't necessarily call it a fix, but I do have a workaround. 

These are the 3 columns I have in my google sheets:

Image File Address	Image File Name	Image File ID
Received_Images/2fb8bc714210.Listing Image 1.160946.jpg	2fb8bc714210.Listing Image 1.160946.jpg

[Image File Address] is just the address generated by Appsheet when you add a photo.

The formula for [Image File Name] is:

=IFERROR(INDEX(SPLIT(Z2,"Images/",,TRUE),0,2),"")

[Image File ID] Column is generated by the script below:

function onOpen() {

  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')

    .addItem('👉 get ID of images', 'getAllIds')

    .addToUi();

}

function getAllIds(){

  var sh=SpreadsheetApp.getActiveSheet()

  for (var i =2;i<=sh.getLastRow();i++){

    id = getFileId(sh.getRange(i,27).getValue())

    try {sh.getRange(i,28).setValue(id)}

    catch(e){sh.getRange(i,28).setValue('not found!')}

  }

}

function getFileId(fileName) {

  var files = DriveApp.getFilesByName(fileName);

  while (files.hasNext()) {

    var file = files.next();

    return (file.getId())

  }

}

[Glooglony_Sloth]

Is there a reason your doing it in the Google Sheet instead of in appsheet column settings > auto compute > like with a  app formula or spreadsheet formula?

I've read there is a problem with timing, like you have to save the image file before you can create a url type issue. And I'm assuming this is the same type of timing issue you get when your trying to convert the fake share url to the real url.

So much for "no code". I've been working on this for 4 days, so I appreciate you giving me hope.

What size px and kb are you getting? , Is there a way to automate clicking on the fake link and then bringing in the real url from the browser somehow?

I tried to see what your doing but don't understand it, I could not get your script to run. Are you creating a shopify acceptable url automatically in your sheet? are you still trying to use the https://lh3.googleusercontent.com method? if so what size images are you getting?

Are you using the "gettablefileurl method that was a dead end for me, although I only tried the automation bot method, which might be out of date.

Do you know what the difference is between the useable  https://lh3.googleusercontent.com  

and the useable, https://drive.google.com which converts the url to https://doc-04-9s-docs.googleusercontent.com/docs/securesc/

Is the first one the "real" large.jpg and the drive is the 600px and less version?

I read on a dropbox post and I think it applies to google drive as well: “public links” are not direct links to the image file itself, but rather to an interstitial page that frames the image and gives you options for downloading the file, etc. Not embeddable at all.  Infact that is where I saw the "change ?dl=0 to ?raw=1 at the end of the URL" method you use or use to use.

I watched a appsheet video that plainly stated they do not want to become a host for videos, I'm starting to get the feeling that they feel the same way about using google drive; but if this is true why wouldn't they just cut to the chase, integrate "no code" and make you sign up for google cloud.

SO HOW DO YOU AUTOMATICALLY CONVERT TO THE "REAL" google URL in bulk?

I can get to a useable one in bulk but it has some manual steps that I know could be automated if I knew even a little of what I was doing.

Like you I'm unable to get a high quality link even after trying several methods; how do you get to display either the background size image (which probably won't work cause I think its just used for the app details view), or the Full or Large versions?

Right now until I know how to automate it, found a script that uses your google drive folder id path and will output the below but I have to FIND and REPLACE the middle part file/d/ WITH uc?id= AND also REMOVE ENDING /view?usp=drivesdk at the end of second method with LEFT() to remove the to the right view stuff.

I found a script that can atleast get to the point of giving =hyperlink("https://drive.google.com/file/d/XXXXXXXXXXXXXX/view?usp=drivesdk","X.X.jpg"

Also did you try just the "share" MultiSelect images in bulk where it ouputs a line of comma seperated values?

That also may be an option, however these "real" link are only like 600x337px and only 60 to 30k which is amazing but gets blurry on enlargement.

[Mrak123]

I'm not really too sure about the answers to a lot of your questions. All I can say around the quality of the photos is that they seem to be either the same or of very similar quality to the original quality of the photos taken.

Someone a couple weeks ago replied to one of my other posts with an in app solution, I'll copy a link to it below, although I haven't even tested it out yet, so can't confirm it's functionality:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Get-a-google-drive-file-ID-for-an-image-after-i...

The workaround I use above, generates images in the following format:

https://drive.google.com/uc?id=19ZEmEUnyEGVV3myrtFksgkqqHFvFT0_6

This workaround is seemless, apart from the fact that when I go to download the sheets file as a CSV, just before uploading it, I have to run the app script to generate the image URL's, which takes several minutes to work through.

To use the script, all you have to do is copy the text below and add it into the script editor on sheets. It will the create a menu dropdown on sheets for you, which you click when you want to generate the image URL's:

function onOpen() {

  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')

    .addItem('👉 get ID of images', 'getAllIds')

    .addToUi();

}

function getAllIds(){

  var sh=SpreadsheetApp.getActiveSheet()

  for (var i =2;i<=sh.getLastRow();i++){

    id = getFileId(sh.getRange(i,27).getValue())

    try {sh.getRange(i,28).setValue(id)}

    catch(e){sh.getRange(i,28).setValue('not found!')}

  }

}

function getFileId(fileName) {

  var files = DriveApp.getFilesByName(fileName);

  while (files.hasNext()) {

    var file = files.next();

    return (file.getId())

  }

}

And I fully understand how infuriating this entire problem is, I must have probably spent a couple weeks working at this before I found this workaround.

[Glooglony_Sloth]

Also does anyone know if you use the database how hard its going to be to setup these image links even if your use aws s3 or google cloud? Supposedly you can add this data source in appsheet but you have to be business or enterprise?, and google won't even show their prices on this. You would think some sort of package deal would be had if your using google cloud and appsheet app and appsheet database together, and you would think it would be seamless? Oh and can we have it create the feed for google merchant center while your at it, pretty pretty please.