CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2107

How using urls in cells to loop to collect text from web pages and set value in another column?

function Looping() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Clock'),
      range,
      values_array; 
  
  range = sheet.getRange('A2:A');
  values_array = range.getValues();
  var loop = JSON.stringify(values_array);
  
  for (var key in loop) {
    
    var column = SpreadsheetApp.getActiveSpreadsheet().getRange('B2:B');
    var values = column.getValues();
    var ct = 0;
    while ( values[ct] && values[ct][0] != "" ) {
      ct++;
    }
    
    var webpage = UrlFetchApp.fetch(key).getContentText();
    sheet.getRange(ct+2, 2).setValue(webpage);
  }
}

Column A values are urls

I want to use these values to put in Column B the text that exists on these web pages.

For that I'm trying to create a looping that finds the first empty cell, collects the existing value in the web page and set that value in the cell.

But the error appears saying Exception: Invalid request: http://0.

Because key is delivering numbers instead of url's, what should I do to solve this problem?

Aditional info -> my Column A values are:

URLS
https://int.soccerway.com/national/colombia/copa-colombia/
https://int.soccerway.com/national/brazil/paranaense-1/
https://int.soccerway.com/national/chile/copa-chile/
https://int.soccerway.com/national/colombia/primera-b/

Upvotes: 1

Views: 36

Answers (1)

GoranK
GoranK

Reputation: 1668

var loop = JSON.stringify(values_array);

only gives you a string representation of values_array, which is array of arrays. Use

  var loop = range.getValues().flat();

instead:

function Looping() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Clock'),
      range,
      values_array; 
  
  range = sheet.getRange('A2:A');

  var loop = range.getValues().flat();
  
  for (var key of loop) {
    var column = SpreadsheetApp.getActiveSpreadsheet().getRange('B2:B');
    var values = column.getValues();
    var ct = 0;
    while ( values[ct] && values[ct][0] != "" ) {
      ct++;
    }
    
    var webpage = UrlFetchApp.fetch(key).getContentText();
    sheet.getRange(ct+2, 2).setValue(webpage);
  }
}

Upvotes: 2

Related Questions