How to add a list of hyperlinks in a CSV field to a cell in Google Sheets?-CodePudding

I'm generating a csv file (with thousands of rows) on my local PC. From a Google account, I would like to do a manual Google Sheets>Import to upload the file for my book club group. The data is collected from HTML tables on multiple pages, if that matters.

One of the fields is named "shelves" is essentially tags, and it contains a list of (name, url) tuples. I'd like to modify my Python program to make a list along the lines of
[=HYPERLINK(url, name), =HYPERLINK(url, name), ..., =HYPERLINK(url, name)]
but I can't find any syntax clues. I also tried
['=HYPERLINK("url", "name"), =HYPERLINK("url", "name")', '=HYPERLINK("url", "name"), =HYPERLINK("url", "name")', ...]

Can something like this via importing a CSV file from Google Sheets work or not, in Aug 2022?

Here's a sample CSV row:

,title,title_url,author,author_url,shelves,date_started,date_finished,member_name,member_url,date_added,group_activity,group_book_id_url'
'29,"Luck in the Shadows (Nightrunner, #1)",http://goodreads.com/book/show/74270.Luck_in_the_Shadows,"Flewelling, Lynn",http://goodreads.com/author/show/42110.Lynn_Flewelling,"[('http://goodreads.com/group/bookshelf/group?shelf=read', 'read'), ('http://goodreads.com/group/bookshelf/group?shelf=1-book-of-the-month', '1-book-of-the-month'), ('http://goodreads.com/group/bookshelf/group?shelf=char-royalty-nobility', 'char-royalty-nobi...'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-action-adventure', 'genre-action-adve...'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-epic', 'genre-epic'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-fantasy', 'genre-fantasy'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-romance', 'genre-romance'), ('http://goodreads.com/group/bookshelf/group?shelf=profession-mage-witch-wizard', 'profession-mage-w...'), ('http://goodreads.com/group/bookshelf/group?shelf=theme-cross-dressing', 'theme-cross-dressing'), ('http://goodreads.com/group/bookshelf/group?shelf=theme-nautical', 'theme-nautical'), ('http://goodreads.com/group/bookshelf/group?shelf=theme-on-the-run', 'theme-on-the-run'), ('http://goodreads.com/group/bookshelf/group?shelf=time-historical', 'time-historical')]",1/1/2021,1/31/2021,Marianne ,http://goodreads.com/user/show/marianne,"group activity for 536628',http://goodreads.com/group/show_book/group?group_book_id=536628

So shelves is the field I'm working on. As you can see it has a long list (and edited for brevity):

[('http://goodreads.com/group/bookshelf/group?shelf=read', 'read'),  ('http://goodreads.com/group/bookshelf/group?shelf=genre-action-adventure', 'genre-action-adve...'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-epic', 'genre-epic'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-fantasy', 'genre-fantasy'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-romance', 'genre-romance'), ('http://goodreads.com/group/bookshelf/group?shelf=profession-mage-witch-wizard', 'profession-mage-w...'), ('http://goodreads.com/group/bookshelf/group?shelf=theme-on-the-run', 'theme-on-the-run'), ('http://goodreads.com/group/bookshelf/group?shelf=time-historical', 'time-historical')]

I would like to have a csv-type file that can be manually imported into Google Sheets and have a single cell contain the shelves list in the following fashion: `[=HYPERTEXT('http://goodreads.com/group/bookshelf/group?shelf=read', 'read'), =HYPERTEXT('http://goodreads.com/group/bookshelf/group?shelf=genre-action-adventure', 'genre-action-adve...')]

So that when it is uploaded to Google it displays similarly to an html table cell:

Before I go through a ton of iterations of that, I wanted to see if that would even work. All the research I've done has come up with mostly 2020 info about only being able to do this in the Google Apps environment, or to possibly write a function for the spreadsheet. I did sign up and try the Google Apps environment, but got stuck in setting up credentials.

If not, is there a best approach to somehow accomplish this?

If it is possible, I could use some help on the syntax. Thank you!

CodePudding user response：

I believe your goal is as follows.

You want to upload CSV data of your local PC to your Google Drive.

Here, from your question, the uploading data is as follows.

  [('http://goodreads.com/group/bookshelf/group?shelf=read', 'read'),  ('http://goodreads.com/group/bookshelf/group?shelf=genre-action-adventure', 'genre-action-adve...'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-epic', 'genre-epic'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-fantasy', 'genre-fantasy'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-romance', 'genre-romance'), ('http://goodreads.com/group/bookshelf/group?shelf=profession-mage-witch-wizard', 'profession-mage-w...'), ('http://goodreads.com/group/bookshelf/group?shelf=theme-on-the-run', 'theme-on-the-run'), ('http://goodreads.com/group/bookshelf/group?shelf=time-historical', 'time-historical')]

You want to create a new Spreadsheet and put the formulas like =HYPERLINK("url", "name") to the column "A" of the new Spreadsheet.
You want to achieve this using a python script. And, when the data is uploaded, you don't want to authorize the scopes.

Unfortunately, when the data is uploaded to Google Drive, authorization is required to be used. So, in this answer, in order to achieve your goal, as a workaround, I used Web Apps as a wrapper API. When Web Apps is used, the authorization can be done when the Web Apps is deployed. By this, when the script accesses the Web Apps, the data can be uploaded without authorization. In this case, how about the following method?

Usage:

1. Create a Google Apps Script project.

In order to use Web Apps, please create a new Google Apps Script project. When you access https://script.google.com/home and create a new project. You can create a new Google Apps Script project.

2. Sample script.

Please copy and paste the following script to the script editor of the created Google Apps Script project.

function doPost(e) {
  const data = JSON.parse(e.postData.contents);
  const newSS = SpreadsheetApp.create("sample");
  const formulas = data.map(([a, b]) => [`=HYPERLINK("${a}", "${b}")`]);
  newSS.getSheets()[0].getRange(1, 1, formulas.length, 1).setFormulas(formulas);
  return ContentService.createTextOutput(newSS.getUrl());
}

In this case, as a sample, a new Spreadsheet is created to the root folder.

If you want to achieve your goal without using =HYPERLINK(), please modify as follows.

From

  const formulas = data.map(([a, b]) => [`=HYPERLINK("${a}", "${b}")`]);
  newSS.getSheets()[0].getRange(1, 1, formulas.length, 1).setFormulas(formulas);

  const rValues = data.map(([a, b]) => [SpreadsheetApp.newRichTextValue().setText(b).setLinkUrl(a).build()]);
  newSS.getSheets()[0].getRange(1, 1, rValues.length).setRichTextValues(rValues);

3. Deploy Web Apps.

The detailed information can be seen at the official document.

Please set this using the new IDE of the script editor.

On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
Please click "Select type" -> "Web App".
Please input the information about the Web App in the fields under "Deployment configuration".
Please select "Me" for "Execute as".
Please select "Anyone" for "Who has access".
Please click "Deploy" button.
Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.
- When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
- You can see the detail of this in the report "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

4. Testing:

In order to test the above Web Apps, in this case, from your question, a python script and your showing data are used. The sample script is as follows. Before you use this, please set url.

import json
import requests

url = "https://script.google.com/macros/s/###/exec" # Please replace this with your Web Apps URL.

# This data is from your question.
data = [('http://goodreads.com/group/bookshelf/group?shelf=read', 'read'),  ('http://goodreads.com/group/bookshelf/group?shelf=genre-action-adventure', 'genre-action-adve...'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-epic', 'genre-epic'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-fantasy', 'genre-fantasy'), ('http://goodreads.com/group/bookshelf/group?shelf=genre-romance', 'genre-romance'), ('http://goodreads.com/group/bookshelf/group?shelf=profession-mage-witch-wizard', 'profession-mage-w...'), ('http://goodreads.com/group/bookshelf/group?shelf=theme-on-the-run', 'theme-on-the-run'), ('http://goodreads.com/group/bookshelf/group?shelf=time-historical', 'time-historical')]

res = requests.post(url, json.dumps(data))
print(res.text)

From your question, I understood that you have already had data in the above script. So, I used it.
When this script is run, your data is sent to Web Apps without the authorization. Because the authorization has already been done when Web Apps is deployed. The uploaded data is parsed and set the values as the formula. And, the URL of the created new Spreadsheet is returned.

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in the report "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

References:

CodePudding user response：

Google's Import function cannot automatically translate a field containing a list of URLs into a single field as a list of Hyperlinks, regardless of the URL format used in the CSV field.

The user must write a Google Sheet Extension using Extensions > Apps Scripts in the relevant Sheet. Thanks to Tanaike's Answer that supplies comprehensive directions.

The user can then copy that script to their other Google Sheets that Import similarly formatted files.