How to format AWS regions/AZs data into table?-CodePudding

I am trying to turn the Region, AZs and Year Launched data on the

Why did this happen and how can I fix it? Also, what can I do to remove the redundant text like Map Key, Regions, Edge Locations, etc.? I only need "...(Region)", "AZs:..." and "Launched ...".

Finally, if possible, how can I put "Edge locations", "AWS Local Zones" and "Regional Edge Caches" from

Far from correct

I used TRANSPOSE(SPLIT(IMPORTXML(...),";") to separate the cities but what happened instead was only the text "Reliable, low latency and high throughput network connectivity" remained.

Thanks in advance for any help...

CodePudding user response：

To retrieve the locations, in A1

https://aws.amazon.com/cloudfront/features/?p=ugi&l=na&whats-new-cloudfront.sort-by=item.additionalFields.postDateTime&whats-new-cloudfront.sort-order=desc

in A2

//div[@class='lb-txt-none lb-txt-18 lb-txt']|//div[@class='lb-rtxt']/p

and finally

=query(arrayformula(flatten(trim(split(query(importxml(A1,A2),"select Col2 where Col2 is not null "),";")))),"select * where Col1 is not null")

may be improved by adding Regional Edge Caches ...

CodePudding user response：

A solution with script can give you a database

function listOfLocations(url) {
  var source = UrlFetchApp.fetch(url).getContentText().replace(/(\r\n|\n|\r|\t|<br>|&nbsp;|&amp;|  )/gm," ");
  var result=[]
  result.push(['region','type','location'])
  var regions = source.split('<div class="lb-txt-none lb-txt-18 lb-txt">')
  var n1=0
  regions.forEach(function(region){
    if (n1  >0) {
      var myRegion = region.split('</div>')[0].trim()
      var types = region.split('<p>')
      var n2=0
      types.forEach(function(type){
        if (n2  >0) {
          try{
            var myType = type.split('<b>')[1].split('</b>')[0].trim()
            var locations = type.split('</b>')[1].split('</p>')[0].split(';')
            locations.forEach(function(location){
              var myLocation = location.trim()
              if (myLocation != '' && (myLocation.indexOf('CloudFront') == -1)){
                result.push([myRegion,myType,myLocation])
              }
            })
          }
          catch(e){}
        }
      })
    }
  })
  return result
}

in A1:

https://aws.amazon.com/cloudfront/features/?p=ugi&l=na&whats-new-cloudfront.sort-by=item.additionalFields.postDateTime&whats-new-cloudfront.sort-order=desc

in A2:

=listOfLocations(A1)