TypeError: fromSht.getDataRange is not a function

[David Suárez]

Hello I a code to sopy from one sheet to another, it works if I run this manually but when I try to schedule always received the error:

TypeError: fromSht.getDataRange is not a function at vlookup_MT2(Buscar MT2:23:27)

The code is the nest:

const aa = SpreadsheetApp.openById("1OzXv8HZYxaVYYqC7kCvibFQdnZ7qu7cZDiv-Gge18N4")

const cc=SpreadsheetApp.openById("1sX3B5dTx5AjGzAT5TRVm03St-WwOjsKoNwAmoe8op1M");

/**

 * @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from

 * @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to

 * @param {Number} fromCompCol -Column number of fromSht to compare

 * @param {Number} toCompCol -Column number of toSht to compare

 * @param {Number} fromCol -Column number of fromSht to get result

 * @param {Number} toCol -Column number of toSht to get result

 * @author https://stackoverflow.com/users/8404453

 */

function vlookup_MT2(

  fromSht = aa.getSheetByName('VariantID'),

  toSht = cc.getSheetByName('Ordenes'),

  fromCompCol = 1,

  toCompCol = 6,

  fromCol = 23,

  toCol = 17

 

) {

  const toShtLr = toSht.getLastRow();

  const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();

  const fromArr = fromSht.getDataRange().getValues();

  fromCompCol--;

  fromCol--;

  /*Create a hash object of fromSheet*/

  const obj1 = fromArr.reduce((obj, row) => {

    let el = row[fromCompCol];

    el in obj ? null : (obj[el] = row[fromCol]);

    return obj;

  }, {});

  //Paste to column

  toSht

    .getRange(2, toCol, toShtLr - 1, 1)

    .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));

}

[CBMServices Web]

This is basically telling you that the sheet does not exist.

Do you have a sheet with this specific name "Variant ID". It needs to be exact spelling including upper/lower case.

fromSht = aa.getSheetByName('VariantID')

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c26cc091-5aa0-40d1-bf1e-a64473454704n%40googlegroups.com.

[David Suárez]

Yes, the  name of sheet is exactly " VariantID",

The weird is that it works if I run the function manually.

[CBMServices Web]

Ok but the code has a space between variant and ID.

Make sure there is a space in the name of the sheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7d86e592-5784-4116-b01e-9baf88ccadb8n%40googlegroups.com.

[cbmserv...@gmail.com]

If the name is exactly the same, then the only thing I can think of is how he is defining his function parameters. In general, I never do that as it is bad practice (in my opinion).

 

Try moving all the parameters to be variable instead (which they should be).

 

const aa = SpreadsheetApp.openById("1OzXv8HZYxaVYYqC7kCvibFQdnZ7qu7cZDiv-Gge18N4")

const cc = SpreadsheetApp.openById("1sX3B5dTx5AjGzAT5TRVm03St-WwOjsKoNwAmoe8op1M");

/**

 * @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from

 * @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to

 * @param {Number} fromCompCol -Column number of fromSht to compare

 * @param {Number} toCompCol -Column number of toSht to compare

 * @param {Number} fromCol -Column number of fromSht to get result

 * @param {Number} toCol -Column number of toSht to get result

 * @author https://stackoverflow.com/users/8404453

 */

function vlookup_MT2() {

 

  var   fromSht = aa.getSheetByName('VariantID'),

        toSht = cc.getSheetByName('Ordenes'),

        fromCompCol = 1,

        toCompCol = 6,

        fromCol = 23,

        toCol = 17;

 

  const toShtLr = toSht.getLastRow();

  const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();

  const fromArr = fromSht.getDataRange().getValues();

  fromCompCol--;

  fromCol--;

 

  /*Create a hash object of fromSheet*/

  const obj1 = fromArr.reduce((obj, row) => {

    let el = row[fromCompCol];

    el in obj ? null : (obj[el] = row[fromCol]);

    return obj;

  }, {});

 

  //Paste to column

  toSht

    .getRange(2, toCol, toShtLr - 1, 1)

    .setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));

}

 

You can also add in some debugging statements so that you get a better view of what is going on.

 

Add this before the offending statement:

 

Logger.log(JSON.stringfy(fromSht));

 

It should give you a view of what is in that variable and if it is an object pointing to a sheet or not.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7d86e592-5784-4116-b01e-9baf88ccadb8n%40googlegroups.com.

[David Suárez]

Thanks a lot.

I will try this.

david suarez Analista de Inteligencia de Negocios
www.masayacompany.com
Planté más de 1 millón de árboles y contando.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/crzKxR8AM58/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/007301d9cda5%24ef42a340%24cdc7e9c0%24%40gmail.com.

[Cooper Smith]

The problem i that you have five parameter in your function declaration and the first one always gets replaced by the trigger event object