logo
down
shadow

Google Script pulling dynamic HTML iFrame from Google Sheet Cell


Google Script pulling dynamic HTML iFrame from Google Sheet Cell

By : JBarbot
Date : November 19 2020, 03:01 PM
seems to work fine Get some HTML code from a Cell in a Spreadsheet You need to update the variables iframeSheet and iframeCell. Create the iframe statement. Deploy as a web app. The doGet is already there. You can also run the showIframeDialog() function to see it work.
The Code:
code :
function getIframeCode() 
{

    var iframeSheet='Sheet1';
    var iframeCell='A1';
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getSheetByName(iframeSheet);
    var hl=sh.getRange(iframeCell).getValue();
    return hl;
}

function showIframeDialog()
{
  var ui=HtmlService.createHtmlOutputFromFile('iframecode');
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Dynamic Iframe')
}

function doGet()
{
  var ui=HtmlService.createHtmlOutputFromFile('iframecode');
  return ui.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script>
    $(function() {
        google.script.run
          .withSuccessHandler(updateIframeCode)
          .getIframeCode();
      });
    function updateIframeCode(hl)
    {
      document.getElementById("iframe").innerHTML=hl;
    }    
    console.log("My code");
  </script>
  </head>
  <body>
  <div id="iframe"></div>
 </body>
</html>


Share : facebook icon twitter icon
Changing font-weight of partial cell value in Google Sheet using Google App script

Changing font-weight of partial cell value in Google Sheet using Google App script


By : Oleg Dovzhenko
Date : March 29 2020, 07:55 AM
hope this fix your issue Unfortunately there is no way (as of now) you can modify part of a cell programically through Apps Script. Here is an issue already going on for this.
https://issuetracker.google.com/issues/36764247
Apps Script: Why is my function to save Google Sheet as PDF pulling "REF" for cell values with formulas?

Apps Script: Why is my function to save Google Sheet as PDF pulling "REF" for cell values with formulas?


By : Tifa Yang
Date : March 29 2020, 07:55 AM
To fix the issue you can do Assuming that the sheets you delete are used in a formula on the sheet you want to keep, you need to ensure that your sheet only contains values.
Add two lines of code:
code :
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(folder));
var source = destSpreadsheet.getSheetByName(sheetName).getDataRange; //new code
source.copyTo (source, {contentsOnly: true}); //new code
var sheets = destSpreadsheet.getSheets();
Prompt input on double click on cell in google sheet using google script

Prompt input on double click on cell in google sheet using google script


By : user3529924
Date : March 29 2020, 07:55 AM
will help you I'd like to prompt an input box when I double click on a cell in Google Sheet. The perfect example is when you protect a range in Google Sheet, whoever tries to double click on the protected range gets a prompted message box. I want to do the same with a input box instead. , I FOUND A WORKAROUND
onEdit triggers this function
code :
function setCell(){
  //e.source.toast('flag1');
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("*** SEARCH ***")
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");
  var cel = sheet.getActiveCell();    
  var val = cel.getValue();
  var row = cel.getRow();
  var col = cel.getColumn();

  if(sh.getSheetName() == "Contacts" == true){
    Logger.log('nope');
  }

  else if(sh.getSheetName() == "*** SEARCH ***" == true & row > 7){

    cel.setValue("");
    var lule = sheet.getRange(row,col).getValue();
    var line = sheet.getRange(row,1,1,1).getValue();
    sheet.getRange("A4").setValue(line)
    sheet.getRange("A5").setValue(col)

        ss.getRange(line+4,col).setValue(val);
  }
}
function clearCell(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("*** SEARCH ***")
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");
  var cel = sheet.getActiveCell();    
  var val = cel.getValue();
  var row = cel.getRow();
  var col = cel.getColumn();

  if(sh.getSheetName() == "Contacts" == true){
    Logger.log('nope');
  }

  else if(sh.getSheetName() == "*** SEARCH ***" == true & row > 7){

    var lule = sheet.getRange(row,col).getValue();
    var line = sheet.getRange(row,1,1,1).getValue();
    sheet.getRange("A4").setValue(line)
    sheet.getRange("A5").setValue(col)
       ss.getRange(line+4,col).clear({contentsOnly: true});
  }
}
Google App Script + Google Sheet - Loop over each cell in active sheet and add formula with local cell address

Google App Script + Google Sheet - Loop over each cell in active sheet and add formula with local cell address


By : user3610639
Date : March 29 2020, 07:55 AM
hope this fix your issue You can retrieve the A1 notation of each cell in your range and use it for the local address in your formula.
Sample:
code :
function myFunction() {
  var IMPORTRANGE_BEGINFORMULA_VALUE = "=IMPORTRANGE";
  var SPREADSHEETURLVALUE = "my.url.com"
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var IMPORTRANGE_INFORMATIONTAB_VALUE = activeSheet.getName();
  var rangeList = activeSheet.getRange('A1:AA69');
  var array=[];
  for( var i = 0; i < rangeList.getLastRow(); i++){
    array[i]=[];
    for( var j = 0; j < rangeList.getLastColumn(); j++){    
      var notation = activeSheet.getRange(i+1,j+1).getA1Notation();
      var notation = rangeList.getCell(i+1,j+1).getA1Notation();
      // Create a combined formula from above variables, at present the only part missing is the cell to inject this into and closing parenthesis.
      var IMPORTRANGE_FORMULACONSTRUCT = IMPORTRANGE_BEGINFORMULA_VALUE + "(" + '"' + SPREADSHEETURLVALUE + '"' + ", " + '"' + IMPORTRANGE_INFORMATIONTAB_VALUE + "!" + notation + '")' ;
      // Print to Logs (View with Ctrl + Enter)
      array[i].push(IMPORTRANGE_FORMULACONSTRUCT);
    }
  }
  rangeList.setValues(array);
}
Google App Script + Google Sheet - Fetch URL from cell (which links to separate sheet) and pull in sheet tab from linked

Google App Script + Google Sheet - Fetch URL from cell (which links to separate sheet) and pull in sheet tab from linked


By : iaseer
Date : March 29 2020, 07:55 AM
shadow
Privacy Policy - Terms - Contact Us © voile276.org