Google Sheets Scrips getFormula assistance

Question

I've been working on a small search tool in Google Sheets. This is a project I have created to learn about GS and hopefully take some of my learns into work.

Munkey has been helping me over the last couple of weeks develop my skills and understanding.

I have linked below my "Database" and search tool.

Database - https://docs.google.com/spreadsheets/d/1K53LOopwAJuOVPJ5RXgpmEO7L3JPHnW5Fx2qp6_3kqo/edit?usp=sharing

Search tool - https://docs.google.com/spreadsheets/d/1mY13e-75dBYfKgkjV8dFFFEvxC838nGNxPrUdusc0PA/edit?usp=sharing

I have the Script below "pushing" data from the Database to the search tools databases to be utilised by the Vlookup function.

function getOvenDataV4(){

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Export Data')
      .addItem('Export Oven Data Now', 'getOvenDataV4')
      .addToUi();

  var settingsSheetName = "oven_settings";

  /* DO NOT EDIT BELOW THIS LINE */  


  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settings = ss.getSheetByName(settingsSheetName); // this loads the settings sheet
  var targetSheet = settings.getRange("C2").getValue(); // this gets the     target sheet name from the settings sheet

  var sSheets = settings.getRange("B2:B").getValues(); // this gets the     values in column B in settings, the source sheet names
  var sourceSheets = []; // this is where we will store the sheet names once     we make sure they are not blank


  // this loops makes sure that there is data in Column B in the settings     sheet, if there is, push it to sourceSheets Array
  for(var i = 0; i < sSheets.length;i++){
    if(sSheets[i][0]!=""){
       sourceSheets.push(sSheets[i]);
    }
  }

var dKeys = settings.getRange("A2:A").getValues(); // This gets the values     of column A in settings, the spreadsheet keys/IDs
var sKeys = []; // this is where we will store the ID's/keys that are not     blank for later


  // this loop makes sure that column A, the spreadsheet IDs/keys,not     blank, if they are not blank. Lets push them into the sKeys Array
for(var i = 0; i < dKeys.length;i++){
  if(dKeys[i][0]!=""){
    sKeys.push(dKeys[i]);
 }

}

var data = []; 

for (var i= 0; i<sourceSheets.length;i++){

  var values = ss.getSheetByName(sourceSheets[i]).getDataRange().getValues();     

  for (var x = 1;x < values.length; x++){
      if(values[x][0]!= ""){
        data.push(values[x]);
      }
  }
}

  // below loops through all your keys, opens that sheet by ID, which we have, and opens the target sheet, which we have and writes the data
for (var i = 0; i< sKeys.length;i++){
    var tss = SpreadsheetApp.openById(sKeys[i]);
    var target =  tss.getSheetByName(targetSheet);
    var range = target.getRange(2,1, data.length, data[0].length);    target.getRange(2,1,target.getLastRow()-1,target.getLastColumn()).clearContent(); 
    range.setValues(data); 
  }
}

Munkey made some comments that are added to the above snippit to help me understand how it all worked. The script works fantastic and as intended. However, it wont pull the data through from my =image("") formula in column "H".

Can anyone offer any help on how to adapt the above script to pull the =Image("") formula to the search tool? Similar to how =importrange does it?

I've tried to search this, including the google dev site, and a variety of different methods to do this using the getFormula and getFormulas methods, but nothing has seemed to work so far. Most likely I'm just using them wrong :)

Munkey Munkey · Accepted Answer · 2015-05-07T23:35:42

This is possibly a clumsy solution, but I think it works. It creates an array called row, and arranges the data in a way you require. Adding both values and formulas to the row array.

Really open to suggestions on how to improve this.

function getOvenDataV6() {

  var settingsSheetName = "monkey_settings";

  /* DO NOT EDIT BELOW THIS LINE */

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settings = ss.getSheetByName(settingsSheetName);

  // this bit has been edited, note the getValues, not getValue, as we want the whole column now not just a single cell.
  var targetSheetsValues = settings.getRange("C2:C").getValues(); // this gets the target sheet names from the settings sheet
  var targetSheets = []; // And array added to throw target sheet names into, as there is more than one.

  // the reason we use arrays and loops (later on), is because the script has no idea how much data to expect.
  // so we go through whatever it's grabbed, the stuff it thinks is data, but we check it later on.
  // only a simple check. Our check is that it cannot be blank. ""
  // then stuff it in an array, a handy thing to store data, for use later on.

  var sSheets = settings.getRange("B2:B").getValues();
  var sourceSheets = [];

  // new loop below to get the target sheets names. We'll use this in the write bit later.

  for (var i = 0; i < targetSheetsValues.length; i++) {
    if (targetSheetsValues[i][0] != "") {
      targetSheets.push(targetSheetsValues[i]);
    }

  }

  for (var i = 0; i < sSheets.length; i++) {
    if (sSheets[i][0] != "") {
      sourceSheets.push(sSheets[i]);
    }

  }

  var dKeys = settings.getRange("A2:A").getValues();
  var sKeys = [];

  for (var i = 0; i < dKeys.length; i++) {
    if (dKeys[i][0] != "") {
      sKeys.push(dKeys[i]);
    }

  }

  var data = [];
  for (var i = 0; i < sourceSheets.length; i++) {

    // below has changed from tss.getSheetByName() to ss.getSheetByName() as we are reading data from local sheeet

    var values = ss.getSheetByName(sourceSheets[i]).getDataRange().getValues();
    var formula = ss.getSheetByName(sourceSheets[i]).getDataRange().getFormulas();

    for (var x = 1; x < values.length; x++) {
      var row = [
        [values[x][0]],
        [values[x][1]],
        [values[x][2]],
        [values[x][3]],
        [values[x][4]],
        [values[x][5]],
        [values[x][6]],
        [formula[x][7]]
      ];

      if (values[x][0] != "") {
        data.push(row);
      }

    }

  }

  // Below is an array of your column headers, the script was being annoying when clearing sheet data, so decided to clear the whole damn sheet
  // then write the headers via here instead
  var headers = [
    ["Model No", "Product Code", "Brand", "Model No", "kW", "Amp", "Apeture", "Image"]
  ];

  for (var i = 0; i < sKeys.length; i++) {
    var tss = SpreadsheetApp.openById(sKeys[i]);
    for (var x = 0; x < targetSheets.length; x++) { // this loop, within the keys loop, goes through the target sheets array
      var target = tss.getSheetByName(targetSheets[x]); // this loads the target sheet, one by one
      var range = target.getRange(2, 1, data.length, data[0].length); // this gets the cells to write to
      target.clearContents(); // clear the sheet before writing data
      target.getRange("A1:F1").setValues(headers); // write the headers to a1:F1 in target sheet

      range.setValues(data); //write the data
    }
  }
}

the only real change from the original code being this.

 for (var i= 0; i<sourceSheets.length;i++){


// below has changed from tss.getSheetByName() to ss.getSheetByName() as we are reading data from local sheeet

    var values = ss.getSheetByName(sourceSheets[i]).getDataRange().getValues();
    var formula = ss.getSheetByName(sourceSheets[i]).getDataRange().getFormulas();



    for (var x = 1;x < values.length; x++){
      var row = [  [values[x][0]], [values[x][1]], [values[x][2]], [values[x][3]],[values[x][4]],[values[x][5]],[values[x][6]],[formula[x][7]]   ];     



      if(values[x][0]!= ""){
        data.push(row);
      }


  }




}

Google Sheets Scrips getFormula assistance

1 Answers