CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
ana maria
ana maria

Reputation: 373

How to extract the url from a cell Spreadsheet to string and use DocumentApp.openByUrl

In a Spreadsheet cell I have a link to a Google doc and I want to recuperate it in order to open the Google Doc and modify it. So in my cell I've put this format https://docs.google.com/document/d/1Gb48I...... (without the /edit in the end) and I've tried 

            var body = '=HYPERLINK("'data[n][COLUMN_URL-1]'+'/edit'")'.getBody();
            var body = '=HYPERLINK("'data[n][COLUMN_URL-1]'+'/edit'")'.getBody();

           // this one works but I want to use data[n][COLUMN_URL-1] because I have several Google Docs links
var body = DocumentApp.openByUrl('https://docs.google.com/document/d/1Gb48IXos......../edit').getBody(); 
            if(body){
//edit the Google doc

If you have ideas what to do thank you because with the concatenation of the /edit(3rd line of code) it works

Edit: The cell is not formatted(hyperlinked)/formula i have only the https://...

Edit 2: I've tried with a code from Suhail Ansari thank you but I have an error that the document is missing and I don't have nothing for the logs if you have others ideas:

var COLUMN_URL = 10 ;

function getIdFrom(url) {
  var id = "";
  var parts = url.split(/^(([^:\/?#]+):)?(\/\/([^\/?#]*))?([^?#]*)(\?([^#]*))?(#(.*))?/);
  if (url.indexOf('?id=') >= 0){
    id = (parts[6].split("=")[1]).replace("&usp","");
    return id;
  } else {
    id = parts[5].split("/");
    //Using sort to get the id as it is the longest element. 
    var sortArr = id.sort(function(a,b){return b.length - a.length});
    id = sortArr[0];
    return id;
  }
}

var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
  var numRows = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var data = sheet.getRange(1,1,numRows,lastColumn).getDisplayValues();
   for(n=1;n < data.length;n++) {
  var URL =data[n][COLUMN_URL-1];
        Logger.log('The URL  ',URL);
        var id = getIdFrom(URL);
        Logger.log('The ID  ',id);
        var body = DocumentApp.openById(id).getBody();
        
        
        if(body)
        {......//edit Google Doc

This is the 10 column with the URLs from the Google Docs here

In fact I have docs.google.com/open?id=1qo0B_HCbjcBrYyJ ... as URL for the others rows I tried to do by hand for 3rd one as you can see in the picture

Edit 3: So I am very confused in this moment because the column I have in the spreadsheet commes from a loop where I put the URL of the Google Docs in the cell like this : var trange = sheet.getRange.. trange.setValue(doc.getUrl()); and now if i look at the table i have the links in this format

https://docs.google.com/open?id=1RYRVotAq6IOz5tys1krnENfgN_pU0KYuUzR24i so now if i want to get back the Google Doc I have the following :

 //  var body = DocumentApp.openByUrl('https://docs.google.com/open?id=1Xb4QjiWwpn2TJ8-9kkIhU6m1rgmb48g6xYVopN').getBody();
            //var body = DocumentApp.openByUrl('https://docs.google.com/1Xb4QjiWwpn2TJ8-9kkIhU6m1rgmb48g6xYVopN/edit').getBody();
            var body = DocumentApp.openByUrl('https://docs.google.com/document/d/1Xb4QjiWwpn2TJ8-9kkIhU6m1rgmb48g6xYVopN/edit').getBody();

only the 3rd one works.Where I am doing wrong? Because I want to
for(n=1;n < data.length;n++) {... make a loop for and var URL = data[n][COLUMN_URL-1];

Upvotes: 0

Views: 1066

Answers (1)

Suhail Ansari
Suhail Ansari

Reputation: 92

You could add some code for getting the Doc ID from the URL and then open the doc by using

DocumentApp.openById(id);

Here is a link to do that.

A sample code below with help from the above linked answer:

function myFunction() {
  var URL = SpreadsheetApp.getActiveSheet().getRange('A1').getValue();
  var id = getIdFrom(URL);

  var doc = DocumentApp.openById(id);

  var body = doc.getBody();
  Logger.log(body.getText());
}


function getIdFrom(url) {
  var id = "";
  var parts = url.split(/^(([^:\/?#]+):)?(\/\/([^\/?#]*))?([^?#]*)(\?([^#]*))?(#(.*))?/);
  if (url.indexOf('?id=') >= 0){
     id = (parts[6].split("=")[1]).replace("&usp","");
     return id;
   } else {
   id = parts[5].split("/");
   //Using sort to get the id as it is the longest element. 
   var sortArr = id.sort(function(a,b){return b.length - a.length});
   id = sortArr[0];
   return id;
   }
 }

Demo Google Sheet

Upvotes: 1

Related Questions