Search code examples
javascriptnode.jsgoogle-sheetsgoogle-cloud-platformgoogle-sheets-api

How do I read the underlying link in a cell using Node and the Google sheets v4 api?

I'm developing a node.js web app on GCP App Engine, and part of it requires reading a Google sheet. Some cells in this sheet have plain text and then an underlying link (so the cell might say "link" but when you hover over it you'll see the URL "www.url.com".) I've tried messing around with the "valueRenderOption" parameter but it doesn't seem to do anyting, I always just get the plain text value of the cell. Does anyone know how to get the underlying URL? Code I'm using is below. Thanks!

const auth = new google.auth.GoogleAuth({
  keyFile: "./keys/key.json", //the key file
  scopes: "https://www.googleapis.com/auth/spreadsheets", 
});
const authClientObject = await auth.getClient();
const googleSheetsInstance = google.sheets({ version: "v4", auth: authClientObject });

const spreadsheetId = ""

const readData = await googleSheetsInstance.spreadsheets.values.get({
  auth, //auth object
  spreadsheetId, // spreadsheet id
  range: "Sheet!D:D", //range of cells to read from.
  valueRenderOption: "FORMULA", // get formatted text
});
Solution

  • From your situation, I guessed that in your situation, the hyperlink might not be put as a formula of =HYPERLINK("https://###/","link"), and it might be directly set the hyperlink to the cell. If my understanding is correct, the hyperlinks cannot be retrieved by "Method: spreadsheets.values.get". In this case, it is required to use "Method: spreadsheets.get".

    When this is reflected in your script, how about the following modification?

    From:

    const readData = await googleSheetsInstance.spreadsheets.values.get({
  auth, //auth object
  spreadsheetId, // spreadsheet id
  range: "Sheet!D:D", //range of cells to read from.
  valueRenderOption: "FORMULA", // get formatted text
});

    To:

    const res = await googleSheetsInstance.spreadsheets.get({
  auth, //auth object
  spreadsheetId,
  ranges: "Sheet!D:D",
  fields: "sheets(data(rowData(values(hyperlink,userEnteredValue))))",
});
const obj = JSON.stringify(res.data.sheets[0].data[0].rowData;
console.log(obj));

    • By this modification, the following result is obtained. You can retrieve the hyperlink with the property of hyperlink.

      [
  {"values":[{"userEnteredValue":{"stringValue":"sample1"}}]},
  {"values":[{"userEnteredValue":{"stringValue":"link"},"hyperlink":"https://###"}]},
  ,
  ,
  ,
]

    • For example, when the hyperlink is put using a formula of HYPERLINK, the following value is included.

      {"values":[{"userEnteredValue":{"formulaValue":"=HYPERLINK(\"https://###",\"link\")"},"hyperlink":"https://###"}]},

    References: