Assistance with utilizing Google Sheets scripts for retrieving formulas is needed

Question

Assistance with utilizing Google Sheets scripts for retrieving formulas is needed

I've been working on a small search tool in Google Sheets as a personal project to improve my skills and knowledge of GS. Munkey has been guiding me over the past few weeks and helping me develop my understanding.

Below are the links to 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

Below is the Script that transfers data from the Database to the search tool databases for 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);
  var targetSheet = settings.getRange("C2").getValue(); 

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

  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++){

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

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

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 provided some helpful comments to the script above to aid my understanding. Although the script works well, it doesn't pull data from my =image("") formula in column "H".

Is there anyone who could assist in modifying the script to include the =Image("") formula in the search tool, similar to how =importrange functions? I've tried various methods like getFormula and getFormulas, but with no success so far. It's likely I'm just not using them correctly :)

javascript google-apps-script google-sheets

Answer 1

Answer №1

This solution may not be the most elegant, but it gets the job done by organizing the data into an array called row. This array includes both values and formulas as required.

I am open to suggestions on how to enhance this solution further.

function getOvenDataV6() {

  var settingsSheetName = "monkey_settings";

  /* DO NOT EDIT BELOW THIS LINE */

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

  var targetSheetsValues = settings.getRange("C2:C").getValues();
  var targetSheets = [];

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

  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++) {
    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);
      }
    }
  }

  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++) {
      var target = tss.getSheetByName(targetSheets[x]);
      var range = target.getRange(2, 1, data.length, data[0].length);
      target.clearContents();
      target.getRange("A1:F1").setValues(headers);
      range.setValues(data);
    }
  }
}

The main modification in the code is:

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

    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);
      }
  }
}

Answer 2

This solution may not be the most elegant, but it gets the job done by organizing the data into an array called row. This array includes both values and formulas as required.

I am open to suggestions on how to enhance this solution further.

function getOvenDataV6() {

  var settingsSheetName = "monkey_settings";

  /* DO NOT EDIT BELOW THIS LINE */

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

  var targetSheetsValues = settings.getRange("C2:C").getValues();
  var targetSheets = [];

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

  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++) {
    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);
      }
    }
  }

  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++) {
      var target = tss.getSheetByName(targetSheets[x]);
      var range = target.getRange(2, 1, data.length, data[0].length);
      target.clearContents();
      target.getRange("A1:F1").setValues(headers);
      range.setValues(data);
    }
  }
}

The main modification in the code is:

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

    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);
      }
  }
}

Assistance with utilizing Google Sheets scripts for retrieving formulas is needed

Answer №1

Similar questions

What is the best way to fetch JSON data in React from the start and then manipulate it as needed before exporting to another file within my application?

What could be causing my jQuery handler to not capture my form submission?

Using a JSON key as a parameter in a function

Guide on utilizing exported API endpoint in Node and Express

Guide on changing the order of Vue sibling components when rendering a shared array within a parent component list

Error TS2346: The parameters provided do not match the signature for the d3Service/d3-ng2-service TypeScript function

The model fails to bind when JSON is sent to the MVC controller

Elegant switch in jQuery

What is the best method for swapping out an iframe with a div using Javascript?

Images showing Strava heat maps retrieved through API

What is the best way to retrieve the value of a text box in VUEjs using its unique identifier?

Struggling with obtaining react-modal in my React Component

Trouble with the x-cloak attribute in alpine.js

Determining the orientation of an image in JavaScript

Are memory leaks a common issue with Angular directives?

Currently, I am working on a project and encountering an issue with html2canvas

Having trouble loading an image after successfully connecting to an API with react.js

Jest does not allow mocking a module and validating function invocations at the same time

Retrieving the value of a selected radio button in Asp.net using Javascript

What is the method for accessing an app from a file other than server.js?