CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetshyperlinkspreadsheet
Neo
Neo

Reputation: 39

hyperlinks removed in Google App Script declaration

I'd like some advice on managing hyperlinks in arrays

I have a function that transfers column data from my "Data" Google Sheet spreadsheet to my "Metrics" Google Sheet spreadsheet. This works well enough.

However, the column I'm transferring contains a hyperlink.

(=HYPERLINK("https://www.instagram.com/p/B5wVG5bn_6C/?igshid=1237ljejed19t", "Post Link #2655")

When the value is transferred to the Metrics Sheet, the hyperlink is stripped.

Is there anyway to transfer this data to the other spreadsheet with removing the hyperlink?

Here the function I'm using for it.

Grateful for any insight.

function Feed_Update_Test() {

  var Metrics = SpreadsheetApp.openById("10Wl1B4AtdLHJXBbLbMQbSdtRyAb61biCWYpOQEEywIY"); 
  var Data = SpreadsheetApp.getActiveSpreadsheet(); // FEED Spreadsheet

  var FeedMetricSheet = Metrics.getSheetByName('Feature Metrics'); // Metrics Sheet 
  var FeedDataSheet = Data.getSheetByName("The Feed_Raw"); // Data Sheet

  var FeedDataRange = FeedDataSheet.getRange(2, 1, FeedDataSheet.getLastRow()-1,1); 

  var FeedDataLastRow = FeedDataSheet.getLastRow(); 

  var FeedMetricEmptyRange = FeedMetricSheet.getRange(2,1,FeedDataLastRow-1,1); 

  var FeedDataValues = FeedDataSheet.getRange(2,1,FeedDataLastRow-1,1).getValues(); 

  FeedMetricEmptyRange.setValues(FeedDataValues);

}

Upvotes: 1

Views: 394

Answers (1)

Tanaike
Tanaike

Reputation: 201663

  • You want to copy the values without removing the formulas from the active Spreadsheet to another Spreadsheet using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modified script:

In this case, the data including the values and formulas are put to the destination Spreadsheet. From the formula of your question, I thought that this method can be used. When your script is modified, please modify as follows.

From: 
var FeedDataValues = FeedDataSheet.getRange(2,1,FeedDataLastRow-1,1).getValues();
To: 
var values = FeedDataRange.getValues();
var FeedDataValues = FeedDataRange.getFormulas().map(function([e], i) {return [e || values[i][0]]});

References:

If I misunderstood your question and this was not the result you want, I apologize.

Added:

  • From your replying, question. how do i modify this code to include 17 columns instead of just one?

Unfortunately, I cannot understand about 17 column from your replying. So for example, when you want to retrieve the cells of "A1:Q" which is 17 columns, how about the following modification?

Modified script:

From: 
var FeedDataRange = FeedDataSheet.getRange(2, 1, FeedDataSheet.getLastRow()-1,1); 
var FeedDataLastRow = FeedDataSheet.getLastRow(); 
var FeedMetricEmptyRange = FeedMetricSheet.getRange(2,1,FeedDataLastRow-1,1); 
var FeedDataValues = FeedDataSheet.getRange(2,1,FeedDataLastRow-1,1).getValues(); 
FeedMetricEmptyRange.setValues(FeedDataValues);
To: 
var FeedDataRange = FeedDataSheet.getRange(2, 1, FeedDataSheet.getLastRow()-1, 17);
var values = FeedDataRange.getValues();
var FeedDataValues = FeedDataRange.getFormulas().map(function(e, i) {return e.map(function(f, j) {return f || values[i][j]})});
FeedMetricSheet.getRange(2, 1, FeedDataValues.length, FeedDataValues[0].length).setValues(FeedDataValues);

Upvotes: 0

Related Questions