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>
