0

I have some code which captures the URL of the edit response, so you can click it, and amend the record if needed. It pops out and opens in a new window. I wondered if it was possible to open it directly into the sidebar? I have some code which opens my google form in the side bar, but I want to try and get it to open in the Sidebar....there will be many url links, so I am unsure on how to modify the HTML output file to choose the URL I've clicked, and not a pre defined one? My idea is to be able to import query the table to a spreadsheet, and be able to click the hyperlink, have it appear in sidebar, for me to edit the values, without having to flip between spreadsheets. It'd be pretty cool and would love some guidance.

Form responses code.

var formURL = 'https://docs.google.com/forms/d/yourFOrmIDhere/viewform';
var sheetName = 'Form Responses 1';
var columnIndex = 12; //column to place link in

function getEditResponseUrls() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 2; i < data.length; i++) {
    if (data[i][0] != '' && data[i][columnIndex-1] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if (formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex).setValue(editResponseUrl);
    }
  }
}

SideBar code:

function onOpen()
{
  SpreadsheetApp.getUi().createMenu('My Tools')
    .addItem('createTextEntryForm', 'createTextEntryForm')
    .addToUi();

  loadSideBar();
  SpreadsheetApp.getUi().createMenu('My Menu').addItem('loadSidebar', 'loadSideBar').addToUi();  
}

//This loads the text into the spreadsheet for the html version of the form.
function dispText(txt)
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getSheetByName('Notes');
  var ts=Utilities.formatDate(new Date(), 'GMT-6', "M/dd/yyyy HH:mm:ss");
  var row=[];
  row.push(ts);
  row.push(txt);
  sht.appendRow(row);
  return true;
}

function loadSideBar()
{
  var userInterface=HtmlService.createHtmlOutputFromFile('formBar');//sidebar for html and formBar for form
  SpreadsheetApp.getUi().showSidebar(userInterface);
}


//This is the form
function createTextEntryForm()
{
   var ss=SpreadsheetApp.getActiveSpreadsheet();
   var form=FormApp.create('Form On A Sidebar');
   form.setDescription('Enter Your Message and Push Submit when complete.')
       .setConfirmationMessage('Message Saved and TimeStamped.')
       .setAllowResponseEdits(true)
       .setAcceptingResponses(false)
       .setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
   var containerLink=form.addParagraphTextItem();
   containerLink.setTitle('Enter your comment now.')
       .isRequired();
}

HTML file (formBar.html)

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <iframe src="https://docs.google.com/forms/d/e/YOUR FORM ID HERE/viewform?embedded=true#start=embed" width="300" height="1000" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>
  </body>
</html>

EDIT: Since posting this question, I think I'm close, and I have tried the below...but it's not working correctly. can i call a script to set a url for an iframe?

<!DOCTYPE html>
<html>
  <head>
  <script>

  function loadSideBar() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Form responses 1');
  var cell = ss.getActiveCell();
  var cellrange = cell.getSheet();
  var row1 = cell.getRow();
  var Col1 = cell.getColumn();
  var prize = cellrange.getRange(row1,Col1);
  var value = prize.getValue();


  </script>
    <base target="_top">
  </head>
  <body>



<style>
    #wrap { width: 1620px; height: 2000px; padding: 0; position:relative; left:-150px; top:0px; overflow: hidden; }
    #frame { width: 300px; height: 350px; position:relative; left:-65px; top:0px; }
    #frame { -ms-zoom: 0.7; -moz-transform: scale(0.7); -moz-transform-origin: 0px 0; -o-transform: scale(0.7); -o-transform-origin: 0 0; -webkit-transform: scale(0.7); -webkit-transform-origin: 0 0; }

    .wrap {
    width: 0px;
    height: 0px;
    overflow: hidden;
}

iframe {
    width: 76.92% !important;
    height: 76.92% !important;
    -webkit-transform: scale(0.5);
    transform: scale(0.5);
    -webkit-transform-origin: 0 0;
    transform-origin: 0 0;
}



</style>

<div id="wrap">

   <iframe src=value width="300" height="350" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>


  </div>
  </body>
</html>
  • The question is too broad as the code included on the question have a lot of issues, like calling a function that requires authorization to run, on the same function overwriting the recently created menu, including server side code on cliente side code, etc. By the other hand the question wording is hard to follow. – Rubén Aug 11 at 18:56
1

If I understood the question you want that clicking on a edit response link on your spreadsheet the target be opened on the sidebar. This isn't possible.

Several possible workarounds imply to read the link from a selected cell and use that link to set the iframe src attibute. To read the active cell value you could use

var value = SpreadsheetApp.getActiveRange().getValue();

There are many ways to pass this value to the sidebar, like using google.script.run and templates

Related

References

  • Thanks for your time and I will research these links. :) – Ceebee86 Aug 12 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.