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

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);
    

  • To

      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.

1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
2. Please click "Select type" -> "Web App".
3. Please input the information about the Web App in the fields under "Deployment configuration".
4. Please select "Me" for "Execute as".
5. Please select "Anyone" for "Who has access".
6. Please click "Deploy" button.
7. 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:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script

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.