Can Google Picker API to read the content of g sheet and display it is a specific format?

Question

I would like to have a macro that imports a g sheet and put it in a format that i would define. In order to import a file, I found the Google Picker API . But I could only find highlevel information of the file and not its content. There is the code:

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Import Orbit')
      .addItem('Select File', 'showPicker')
      .addToUi();
}


function showPicker() {
  var html = HtmlService.createHtmlOutputFromFile('dialog.html')
      .setWidth(900)
      .setHeight(500)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModalDialog(html, 'Select a file');
}

function getOAuthToken() {
  DriveApp.getRootFolder();
  return ScriptApp.getOAuthToken();
}

function loadDocInfo(data){
  var ss = SpreadsheetApp.getActive();
  var ws = ss.getActiveSheet();
  ws.clear();
  ws.getRange(1, 1, data.length, data[0].length).setValues(data);
//  ws.getActiveCell().setValue(data[1][2]);
}

function createFile(){
  DriveApp.createFile("Test", "", MimeType.PLAIN_TEXT);
}

And the :HTML

<!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
  <script>
   
    var DEVELOPER_KEY = 'DEVELOPER_KEY';
    var DIALOG_DIMENSIONS = {width: 900, height: 500};
    var pickerApiLoaded = false;

    function onApiLoad() {
      gapi.load('picker', {'callback': function() {
        pickerApiLoaded = true;
      }});
     }

   
    function getOAuthToken() {
      google.script.run.withSuccessHandler(createPicker)
          .withFailureHandler(showError).getOAuthToken();
    }

   
    function createPicker(token) {
      if (pickerApiLoaded && token) {
        var DocsUploadView = new google.picker.DocsUploadView();
        DocsUploadView.setIncludeFolders(true);
        
        var DocsView = new google.picker.DocsView();
        DocsView.setSelectFolderEnabled(true);
        DocsView.setIncludeFolders(true);
        DocsView.setParent("root");
        var picker = new google.picker.PickerBuilder()
            // Instruct Picker to display only spreadsheets in Drive. For other
            // views, see https://developers.google.com/picker/docs/#otherviews
            .addView(DocsView)
            .addView(google.picker.ViewId.DOCUMENTS)
            .addView(google.picker.ViewId.SPREADSHEETS)
            //.addView(DocsUploadView)
            .addView(new google.picker.DocsUploadView())
            // Hide the navigation panel so that Picker fills more of the dialog.
            //.enableFeature(google.picker.Feature.NAV_HIDDEN)
            .enableFeature(google.picker.Feature.MULTISELECT_ENABLED)
            // Hide the title bar since an Apps Script dialog already has a title.
            .hideTitleBar()
            .setOAuthToken(token)
            .setDeveloperKey(DEVELOPER_KEY)
            .setCallback(pickerCallback)
            .setOrigin(google.script.host.origin)
            // Instruct Picker to fill the dialog, minus 2 pixels for the border.
            .setSize(DIALOG_DIMENSIONS.width - 10,
                DIALOG_DIMENSIONS.height - 10)
            .build();
        picker.setVisible(true);
      } else {
        showError('Unable to load the file picker.');
      }
    }



    function pickerCallback(data) {
      var action = data[google.picker.Response.ACTION];
      if (action == google.picker.Action.PICKED) {
        var docInfo = [["Title", "ID", "URL","Country","PDF","No.","Family Members"]];
        
        var docs = data[google.picker.Response.DOCUMENTS];
        for (var i = 0; i < docs.length; i ++){
          var doc = docs[i];
          var id = doc[google.picker.Document.ID];
          var url = doc[google.picker.Document.URL];
          var title = doc[google.picker.Document.NAME];
          var country = doc[google.picker.Document.NAME];
          var pdf = data;
          var nob = docs.length;
          var family = docs[i];

          document.getElementById('result').innerHTML =
            '<b>You chose:</b><br>Name: <a href="' + url + '">' + title +
            '</a><br>ID: ' + id;

          docInfo.push([title, id, url,country,pdf,nob,family]);
        }
        google.script.run.withSuccessHandler(function(){
          google.script.host.close();
        }).loadDocInfo(docInfo);
        
      } else if (action == google.picker.Action.CANCEL) {
        google.script.host.close();
      }
    }

   
    function showError(message) {
      document.getElementById('result').innerHTML = 'Error: ' + message;
    }
  </script>
</head>
<body>
  <div>
    <p id='result'></p>
  </div>
  <script src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
  <script>
    window.onload = function(){
      getOAuthToken();
    }
  </script>
</body>
</html>

What can I do to get the content of the sheet I have selected using the Google Picker API? Are there other means?

EDIT---

function pickerCallback(data) {
      var action = data[google.picker.Response.ACTION];
      if (action == google.picker.Action.PICKED) {
        var docInfo = [["ID", "Value"]];
        
        var docs = data[google.picker.Response.DOCUMENTS];
        for (var i = 0; i < docs.length; i ++){
          var doc = docs[i];
          var id = doc[google.picker.Document.ID];
          var ssraw = SpreadsheetApp.openById(id);
          var value = ssraw.getSheetByName('Sheet1').getRange('A1').getValue();

          document.getElementById('result').innerHTML =
            '<b>You chose:</b><br>Name: <a href="' + url + '">' + title +
            '</a><br>ID: ' + id;

          docInfo.push([id, value]);
        }
        google.script.run.withSuccessHandler(function(){
          google.script.host.close();
        }).loadDocInfo(docInfo);
        
      } else if (action == google.picker.Action.CANCEL) {
        google.script.host.close();
      }
    }

If you have the `id`, use `openById()` – TheMaster Dec 15 '21 at 17:59 — TheMaster, Dec 15 '21 at 17:59

score 1 · Answer 1 · answered Dec 15 '21 at 18:34

1

You can only access the spreadsheet's content by opening the spreadsheet as a Spreadsheet object.

Since you have the id from the Picker API, you can use openById() like this:

var ss = SpreadsheetApp.openById(id);

Then you can access the individual sheets, ranges, and values using the methods in Class Spreadsheet, Class Sheet, and Class Range.

For example:

var value = ss.getSheetByName('Sheet1').getRange('A1').getValue();

answered Dec 15 '21 at 18:34

CMB

4,950
1
4
16

Where exactly do u suggest adding that. I tried adding inside the loop of pickerCallback , it ran without errors but on g sheet it doest even work (blank box after pushing select file) – Salma Kastalli Dec 15 '21 at 19:09
You should still push the value to the ``docInfo`` array. – CMB Dec 15 '21 at 19:39
yes that was done. I limed the table to docInfo.push([id, value]); I posted the code in the edit. The problem remains – Salma Kastalli Dec 15 '21 at 19:56

Can Google Picker API to read the content of g sheet and display it is a specific format?

1 Answers1