Help with script to add hyperlinks daily

[Tracy Pallotta - CDPHE]

Please help!. I've spent a lot of time on it and can't get it to work.

Need: For all of the columns listed in my "Tracker" sheet (these appear to be correct) if a number is entered and it's not already linked, I want the script to add the hyperlink that can be found on the "RawPAFData" sheet. Column A has the corresponding number and column B has the hyperlink.  I plan to set the trigger to run nightly after I download the data and ad to the RawPAFData sheet. 

function updateMissingLinks() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var trackerSheet = spreadsheet.getSheetByName('Tracker');

  var rawPAFDataSheet = spreadsheet.getSheetByName('RawPAFData');

 

  var colIndicesToCheck = {

    'AH': 34,

    'BF': 58,

    'BT': 72,

    'CH': 86,

    'CV': 100,

    'DU': 125,

    'EH': 138,

    'ET': 150

  };

 

 var rawPAFDataValues = rawPAFDataSheet.getDataRange().getValues();

  var currentDate = new Date();

  var oneWeekAgo = new Date();

  oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);

 

  for (var col in colIndicesToCheck) {

    var colIndex = colIndicesToCheck[col];

    var range = trackerSheet.getRange(2, colIndex, trackerSheet.getLastRow() - 1, 1);

    var values = range.getValues();

    var formulas = range.getFormulas();

   

    Logger.log('Processing column: ' + col);

    for (var row = 0; row < values.length; row++) {

      var cellValue = values[row][0];

      var formula = formulas[row][0];

     

      Logger.log('Row: ' + (row + 2) + ', Cell Value: ' + cellValue + ', Formula: ' + formula);

      if (formula === "" && cellValue !== "") {

        var editedDate = trackerSheet.getRange(row + 2, 1).getValue();

       

        if (editedDate instanceof Date && editedDate >= oneWeekAgo && editedDate <= currentDate) {

          var number = cellValue;

          var link = getLinkFromRawPAFData(rawPAFDataValues, number);

         

          Logger.log('Number: ' + number + ', Link: ' + link);

          if (link) {

            var cell = trackerSheet.getRange(row + 2, colIndex);

            cell.setValue(number); // Set the cell value

            cell.setFormula('=HYPERLINK("' + link + '", "' + number + '")'); // Set the hyperlink formula

            // Remove existing data validation (if any)

            cell.clearDataValidations();

          }

        }

      }

    }

  }

  // Save changes and force recalculation of the sheet

  SpreadsheetApp.flush();

  spreadsheet.setActiveSheet(trackerSheet);

  spreadsheet.moveActiveSheet(1);

}

function getLinkFromRawPAFData(rawPAFDataValues, number) {

  for (var row = 0; row < rawPAFDataValues.length; row++) {

    if (rawPAFDataValues[row][0] === number) {

      return rawPAFDataValues[row][1];

    }

  }

  return null;

}

[CBMServices Web]

Can you share a sample spreadsheet with me?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b6293ed0-7f8b-4fb8-8e1a-473b372b65f8n%40googlegroups.com.

[cbmserv...@gmail.com]

Tracy,

 

Took a quick look at your script and it looks good with just one little exception.

 

Change this line:

 

cell.setFormula('=HYPERLINK("' + link + '", "' + number + '")'); // Set the hyperlink formula

 

 

to this:

 

cell.setValue('=HYPERLINK("' + link + '", "' + number + '")'); // Set the hyperlink formula

 

If this does not work, then sharing a sample spreadsheet would help so we can see how the rawdata sheet is formatted.

--

[Tracy Pallotta - CDPHE]

Thank you. Unfortunately, that didn't work. I'm not able to share a sample, but Column A on the RawPAFData sheet is Plain Text along with all the selected colums in the tracker. Column B of the RawPAF data is file name that is hyperlinked. I hope this provides enough information to help me resolve the issue. 

[cbmserv...@gmail.com]

Your script is grabbing text from column 1 to make the hyperlink. Is that where the links are?

 

It would be quickest if you make a sample spreadsheet with fake data and share with us so that we see how it is structured and formatted.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0650f509-4499-44e4-8c6d-353f8d2849bbn%40googlegroups.com.

[Tracy Pallotta - CDPHE]

Here's the sample sheet. I have added your email address as viewer. 

[cbmserv...@gmail.com]

Ok I see the problem.

 

Your sheet rawdata is not formatted as plain text for the link, the link is actually encoded. So a getValues will not be the right way to grab those links.

 

What you need to do is use getRichTextValues() on the range, then on that particular cell, use getLinkUrl() to extract the link from the richTextValue of that cell.

 

Here is code change you can try:

 

 var rawPAFDataValues = rawPAFDataSheet.getDataRange().getValues();

  var rawRichData = rawPAFDataSheet.getDataRange().getRichTextValues();

  var currentDate = new Date();

  var oneWeekAgo = new Date();

  oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);

 

  for (var col in colIndicesToCheck) {

    var colIndex = colIndicesToCheck[col];

    var range = trackerSheet.getRange(2, colIndex, trackerSheet.getLastRow() - 1, 1);

    var values = range.getValues();

    var formulas = range.getFormulas();

   

    Logger.log('Processing column: ' + col);

 

    for (var row = 0; row < values.length; row++) {

      var cellValue = values[row][0];

      var formula = formulas[row][0];

     

      Logger.log('Row: ' + (row + 2) + ', Cell Value: ' + cellValue + ', Formula: ' + formula);

 

      if (formula === "" && cellValue !== "") {

        var editedDate = trackerSheet.getRange(row + 2, 1).getValue();

       

        if (editedDate instanceof Date && editedDate >= oneWeekAgo && editedDate <= currentDate) {

          var number = cellValue;

          var link = getLinkFromRawPAFData(rawPAFDataValues, number, rawRichData);

         

          Logger.log('Number: ' + number + ', Link: ' + link);

 

          if (link) {

            var cell = trackerSheet.getRange(row + 2, colIndex);

            cell.setValue(number); // Set the cell value

            cell.setValue('=HYPERLINK("' + link + '", "' + number + '")'); // Set the hyperlink formula

 

            // Remove existing data validation (if any)

            cell.clearDataValidations();

          }

        }

      }

    }

  }

 

  // Save changes and force recalculation of the sheet

  SpreadsheetApp.flush();

  spreadsheet.setActiveSheet(trackerSheet);

  spreadsheet.moveActiveSheet(1);

}

 

function getLinkFromRawPAFData(rawPAFDataValues, number, rawRichData) {

  for (var row = 0; row < rawPAFDataValues.length; row++) {

    if (rawPAFDataValues[row][0] === number) {

      return rawRichData[row][1].getLinkURL();

    }

  }

  return null;

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4b68f2ef-51e2-447a-b613-72b485d46b75n%40googlegroups.com.

[Tracy Pallotta - CDPHE]

I thought it was fixed, but turns out one of my team members manually added the link. I'll have to wait until tomorrow to test more. Thanks again!

[CBMServices Web]

No problem. Let us know how it goes.

Good luck.

[Pallotta - CDPHE, Tracy]

Unfortunately it still isn't working. I then tried to put the full hyperlink path in column AI of RawPAFData, but that didn't work either. 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/G2Nj--Lg2WU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3Wgj6smhq5TM0Yz0wYkjkA01PksxGQmkcO9--ybqVxCTA%40mail.gmail.com.

[CBMServices Web]

Ok I can take a second look. Is the sample spreadsheet formatted exactly the same as your regular spreadsheet?

[Pallotta - CDPHE, Tracy]

Thank you! Yes, it is. 

--

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/G2Nj--Lg2WU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3WKTyDt_9ZSfEuQmxw01LQ6Mguv6JXopccMujxiZBm%2BJw%40mail.gmail.com.

[CBMServices Web]

Hi Tracy,

 I found the problem, there were some logic bugs in the if statements and a typo in the getLinkUrl function.

Here is the code:

function  updateMissingLinks() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var trackerSheet = spreadsheet.getSheetByName('Tracker');

  var rawPAFDataSheet = spreadsheet.getSheetByName('RawPAFData');

  var colIndicesToCheck = {

    'AH': 34,

    'BF': 58,

    'BT': 72,

    'CH': 86,

    'CV': 100,

    'DU': 125,

    'EH': 138,

    'ET': 150

  };

  var rawPAFDataValues = rawPAFDataSheet.getDataRange().getValues();

  var rawRichData = rawPAFDataSheet.getDataRange().getRichTextValues();

  var currentDate = new Date();

  var oneWeekAgo = new Date();

  var lastRow = trackerSheet.getLastRow();

  oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);

  for (var col in colIndicesToCheck) {

    var colIndex = colIndicesToCheck[col];

    var range = trackerSheet.getRange(2, colIndex, lastRow - 1, 1);

    var values = range.getValues();

    var formulas = range.getFormulas();

    Logger.log('Processing column: ' + col);

 

    for (var row = 0; row < values.length; row++) {

      var cellValue = values[row][0];

      var formula = formulas[row][0];

      Logger.log('Row: ' + (row + 2) + ', Cell Value: ' + cellValue + ', Formula: ' + formula);

      if (formula === "" && cellValue !== "") {

        var editedDate = trackerSheet.getRange(row + 2, 2).getValue();

     

        if (editedDate instanceof Date && editedDate <= oneWeekAgo) {

          var number = cellValue;

          var link = getLinkFromRawPAFData(rawPAFDataValues, number, rawRichData);

          Logger.log('Number: ' + number + ', Link: ' + link);

          if (link) {

            var cell = trackerSheet.getRange(row + 2, colIndex);

            cell.setValue(number); // Set the cell value

            cell.setValue('=HYPERLINK("' + link + '", "' + number + '")'); // Set the hyperlink formula

            // Remove existing data validation (if any)

            cell.clearDataValidations();

          }

        }

      }

    }

  }

  // Save changes and force recalculation of the sheet

  SpreadsheetApp.flush();

  spreadsheet.setActiveSheet(trackerSheet);

  spreadsheet.moveActiveSheet(1);

}

 

function getLinkFromRawPAFData(rawPAFDataValues, number, rawRichData) {

  for (var row = 0; row < rawPAFDataValues.length; row++) {

    if (rawPAFDataValues[row][0] === number) {

      return rawRichData[row][1].getLinkUrl();

    }

  }

  return null;

}

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAAYqrqCu6EvbM1bJAsRdAdHC4%2BwgHfEnGSgnE20D-0UjtiVYug%40mail.gmail.com.

[Pallotta - CDPHE, Tracy]

IT WORKS! I'm so excited, thank you so much! 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3XhM8PSEZNZ24fVaqDbF-YH7%3D_HCxhcz2aspKpM3jRxBw%40mail.gmail.com.

[CBMServices Web]

You are welcome.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAAYqrqDHRoCpm%3DL9BkDnGDBNsCi2cL_XgTKR3gypSKsS_S_smg%40mail.gmail.com.