use the content of a cell like value in the macro

Question

i work on a very little project on google sheet.

it is quiet simple in fact and i try to make fews things less dificults for my friend but they are wors than me.

i dont know if it is something that we can do on google sheet but my purpose is to use the informations in a cell. In this code

function test4v2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A2:B51').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Loyers'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getActiveSheet().getFilter().sort(1, true);
  spreadsheet.getRange('B55').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Kopie von Model de Bilan V3.1'), true);
  spreadsheet.getRange('C2').activate();
  spreadsheet.getRange('B55').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

i want to be to put the rang IN te cell B55 and let the macro use the content off the cell B55 not like a text but like a range.

i'm not sur that what i sai is easy to understand ?

Welcome. Thank you for including the code, however you have not explained the outcome that you want nor what is wrong with this code. The names of the sheets in your Google spreadsheet are clear either. Ignoring the code, please describe what you want to do and what is the problem with `test4v2()`. Please share a copy of your spreadsheet (delete any private or confidential data), and include an example of a successful outcome. — Tedinoz, Mar 02 '20 at 09:32
hello ! i tried to edit my first msg but still be impossible for me, dont know why... first of all thank you to take times for my troubles. here ou can find my sheets : https://docs.google.com/spreadsheets/d/1pf9Q23_dp72_GVpL8LAN4FLmkDGEskTCBgGwPK2pH9Y/edit?usp=sharing my objectif is to writte a macro/script who take the range writte in the cell B55 of "kopie von model de bilan 3.1" and use this ran for select, copy and bye the end past on the celle "C2" o "kopie von model de bilan 3.1" but i really dont know how to do date — Darazu, Mar 02 '20 at 09:58

Tedinoz · Accepted Answer · 2020-03-02T11:57:07.767

You want to take a range described as text Loyers!AZ2:BA51 in a cell.
- The cell is Sheet:"Kopie..." cell B55.
You want to copy the data from that range to a new range.
- The new range is based on a starting cell reference: Sheet:"Kopie..." cell C2.

function so6048464601() {

  // set up the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // declare the kopie sheet
  var kopie = ss.getSheetByName("Kopie von Model de Bilan V3.1");

  // get the value in kopie cell B55
  var kopieC55 = kopie.getRange("B55").getValue();
  //Logger.log("DEBUG: kopieC55 = "+kopieC55); // display the value in cell B55

  // declare the loyers sheet
  var loyers = ss.getSheetByName("Loyers");
  // declare the loyers range based on Kopie cell B55
  var loyersrange = loyers.getRange(kopieC55);
  Logger.log("DEBUG: loyer range described on kopie = "+loyersrange.getA1Notation());

  // get information about the loyer range
  var loyernumRows = loyersrange.getNumRows();
  var loyernumColumns = loyersrange.getNumColumns()
  Logger.log("DEBUG: # of rows: "+loyernumRows+", # of columns: "+loyernumColumns);


  // get the row and colun for beginning of the output range based at kopie Cell C2
  var kopieRange = kopie.getRange("C2");
  var kopieStartRow = kopieRange.getRow();
  var kopieStartColumn = kopieRange.getColumn();
  Logger.log("DEBUG: kopie start row: "+kopieStartRow+", kopieStartColumn"+kopieStartColumn);

  // declare the target range on Kopie
  var kopieTargetRange = kopie.getRange(kopieStartRow, kopieStartColumn, loyernumRows, loyernumColumns);
  Logger.log("DEBUG: kopie TargetRange = "+kopieTargetRange.getA1Notation())

  //copy loyer data to kopie Target Range
  loyersrange.copyTo(kopieTargetRange, {contentsOnly:true});

}

Note the changes between your code and this answer.

the sheet "Kopie..." is declared explicitly
the value of the cell containing the range name is given a variable name kopieC55
kopieC55 is used in the getRange(kopieC55) method on loyers. The debug showing the range address (A1Notation) confirms the variable is taken up as the range.
the elements of the output range on "Kopie..." are used to define the target range.
- Cell C2 yields a start row of 2 and a starting Column of 3
- The number of rows and columns is obtained from loyersrange.
- Together they are used to define a range using the getRange(row, column, numRows, numColumns) method

it work ! so much happy ! thank you so much ! i realise that it is a lot more dificulte that what i excepted in my mind. last question about the script. i try to find somewhere i can find stuff for a trigger using checkbox and i see differente function but i also see that i sould writte these function in an oder spot that where i writte the basic function because i sometime see an "trigger.gs" script. i'm wrong ? — Darazu, Mar 02 '20 at 17:06
The **name** of the file (such as 'trigger.gs') is unimportant, but the name of the trigger function can sometimes be important. You should read about [Triggers](https://developers.google.com/apps-script/guides/triggers/) (Simple and Installable) as well as Event Objects (https://developers.google.com/apps-script/guides/triggers/events). Here is a [link](https://stackoverflow.com/a/54972324/1330560) to an `onEdit` trigger based on a checkbox - it is a VERY simple example but it should set you on a path to understand the issues. — Tedinoz, Mar 02 '20 at 23:45
i spend almost all the day readingg some stuff about the triggers. i didn't understand a lot of thing, so i have fews questions: "onEdit" is supose to be only a trigger or it also need to writte my fonction in ? in fact i wanted to use the check bo because when i was lookin a solution to m problems i understood that i should use checkbox. my objectif is basicly to be able to run a script from a smartphone ( ios or android) in the app google sheet — Darazu, Mar 03 '20 at 00:52
Before I answer, it is important to understand/research the difference between Simple triggers and Installable triggers... `onEdit` -if you create a function called 'onEdit()`, then it will automatically operate as a Simple Trigger. Sometimes you may want the "onEdit" function to be an Installable Trigger, in which case, it doesn't matter what name you give the function because when you install the trigger, you will select the function to be applied. — Tedinoz, Mar 03 '20 at 01:22
"run a script from a smartphone ( ios or android) in the app google sheet" - I have no skill in that area. But I suggest you play with creating an onEdit (any onEdit) to develop the logic, testing (the IF statement) and the resulting outcome. Normally when you have an on edit trigger, you need to make sure that the edit was on the correct sheet, correct cell/row/column, and correct value (a ticked checkbox=TRUE). — Tedinoz, Mar 03 '20 at 01:30

use the content of a cell like value in the macro

1 Answers1