Previous day date to be auto populated in Formula

[Vijay Madhavi]

Hello All,

I need your help in writing one condition in formula.

Column 'I' and Column 'M' have vlookup formulas

Condition - I need the date in those formula to be auto picked up to previous spreadsheet tab date(which in this case is 01-Nov-23) whenever i duplicate the spreadsheet tab and create new tab

Currently i am changing it manually to get the previous data from 01-Nov-23

Test File

Regards,

Vijay

[Vijay Madhavi]

Can someone please help with the below query?

Regards,

Vijay

[Vijay Madhavi]

I am sorry to chase but i need someone assistance to fix this as this is creating manual effort everyday for me.

I am happy to share additional information if needed.

Regards,

Vijay

[Vijay Madhavi]

Can someone please help me with the query?

Column 'I' and Column 'M' have vlookup formulas

Condition - I need the date in the vlookup formula to be auto picked up to previous spreadsheet tab date(which in this case is 01-Nov-23) whenever i duplicate the spreadsheet tab and create new tab

Currently i am changing it manually to get the previous data from 01-Nov-23

Test File

I need an app script to run this program everyday automatically without human intervention.

--
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/4rXeyj6PSPY/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/0b860d8d-08cf-4111-9672-e96ef14aee15n%40googlegroups.com.

--

Regards,

Vijay

[Matías Pco.]

Aquí una lógica simple que te puede funcionar:

Primero;

Obtener todas las hojas y convertir los nombre de las hojas en formato fecha que permitirá obtener la ultima fecha anterior partiendo de la fecha actual que se ejecuta el script.

Segundo;

Pasar la fecha objetivo como parámetro a la función que se encargará de establecer la formula.

Recomiendo encarecidamente que revises al menos los ejemplos de la documentación de Google Apps Script en relación con Google sheet:

https://developers.google.com/apps-script/reference/spreadsheet?hl=en

Por otro lado, llenar de formulas en google sheet afecta considerablemente el rendimiento del archivo, recomendable realizar las operaciones de las formulas con código de google apps script.

Espero que te sirva de apoyo para lograr tu objetivo.

Saludos!!

[Keith Andersen]

Vijay,

I'm contemplating a solution to your date problem. Initially I'm thinking of an app script that reads the tab names and calculates today's date minus one then takes that date and places it in a cell where your formula uses that cell as a reference for the range which would include the tab name.

Question: In that test sheet in column J I have The array formula that I sent you and you said it did not work for you. However, in that test sheet it is working. I compared it against a straight formula and dragging it down in the adjacent column which shows that they are both working the same. I asked because it is better to have an array formula that automatically picks up new data rows as opposed to a script that places the formula and then drags down. The drag down method will not pick up any new data added that day. You will have to run the script each time you add a row. I know it's totally your choice and I'm cool with that. I just wanted to let you know the downfall of not using an array formula.

I will be working on your laundry date problem later on tonight.

Cheers

Keith 

Consulta nuestro Aviso de Privacidad.

--
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/5765db09-8a41-4010-a637-ed2cee6bfc98n%40googlegroups.com.

[Keith Andersen]

Vijay,

Did you get my last email?

Also, in the test file in column M you have a drop down on top of an arrayformula. You can have one or the other but not both.

The date you are talking about is a tab name and therefore part of a range reference. As a simple avenue to have it populated in your array formula you could have a cell with the range an have the VLOOKUP formula reference that cell for its range. You already have to do several steps to duplicate a tab and change its name - one more step to modify the range reference cell seems simpler than a script that must perform some complicated manipulation to convert a tab name to a date, then convert the string to a date object then compare it to today etc. It's not a straight forward previous day comparison either. What happens on a Monday? You would need to test if it's a Monday date to pickup Fridays date to compare. What happens if it's a new year? If you comeback after a holiday? So many variables for on script to consider to work without problems.

So, perhaps the simpler route is a moment to modify one cell at the same time you are manually duplicating a tab and renaming it.

Just some thoughts.

Keith 

[Vijay Madhavi]

Hello Keith,

Sorry for the late reply.

I am looking for the only date change option right now whenever I duplicate a new spreadsheet tab. The other query was resolved a few days back.

Please confirm your available time so that we can close this task.

FYI. I messaged you via hangout chat few days ago.

Regards,

Vijay

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFKdf1JPJUgp6vP%3DoCM_wPLvR_T128smjWs4aR0GrgYCQ%40mail.gmail.com.

--

Regards,

Vijay

[Keith Andersen]

What is the VLOOKUP arrayformula and what column do you need it placed in?

I need this because TAB date is within this range for the VLOOKUP.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ-LwLOqVi6Pt-Otz_kAup9sPqv23T2XAoQQCNBj2f7FrjsBeQ%40mail.gmail.com.

[Maria Clementi, Rodolfo]

In fact, my difficulty lies in automating the printing process. I tried recording a macro to automate printing the contents of a spreadsheet but the recorded macro doesn't work. I would need a script to automatically print the contents of the cell set A1:K72.

the error message that appears is:

17:09:58

Erro

TypeError: Cannot read properties of undefined (reading 'PORTRAIT')

the recorded macro code is:

function Gravar() {

 

var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  var range = sheet.getRange("A1:K72");

  var printOptions = {

    'scale': 1,

    'fitToWidth': true,

    'fitToHeight': false,

    'pageOrientation': SpreadsheetApp.PrintOrientation.PORTRAIT,

    'marginTop': 0,

    'marginBottom': 0,

    'marginLeft': 0,

    'marginRight': 0

  };

  var printerName = null;

  var pdfName = null;

  var pdfFolder = null;

  var printJob = {

    'settings': printOptions,

    'printerId': printerName,

    'title': pdfName,

    'pdfFolder': pdfFolder

  };

  var printRange = range.getA1Notation();

  var printJobId = ss.getId() + '' + sheet.getName() + '' + printRange;

  var ticket = null;

  var response = null;

  try {

    ticket = CloudPrintService.getTicket(printJob);

    response = CloudPrintService.submit(printJob, ticket, range);

    Logger.log(response);

  } catch (e) {

    Logger.log(e);

  }

}

function Imprimir() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('A1:K72').activate();

};

Rodolfo Maria Clementi

Supervisor Brasil

Tel.: +55 47 3717-1404 / Cel.: +55 41 99933-3333

Paulo Litzemberger 1.400 / 89075-335/ Brasil Blumenau -SC

rodolfo....@veolia.com

 

www.veolia.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFpgkM0dER0%2BfemrvwfdsXNujF1Hq83-NpinW4nn9UbTw%40mail.gmail.com.

--------------------------------------------------------------------------------------------

Antes de imprimir este mensaje, asegúrese de que es realmente necesario

Uso del correo electrónico de Veolia

Este correo electrónico y, en su caso, cualquier fichero anexo al mismo, contiene información de carácter confidencial exclusivamente dirigida a su destinatario o destinatarios y propiedad de Veolia y filiales.  Queda prohibida su divulgación, copia o distribución a terceros sin la previa autorización escrita de  Veolia, en virtud de la legislación vigente. En el caso de haber recibido este correo electrónico por error, se ruega notificar inmediatamente esta circunstancia mediante reenvío a la dirección electrónica del remitente y la destrucción del mismo.

Afin de contribuer au respect de l'environnement, merci de n'imprimer ce mail qu'en cas de nécessité.

Usage du courrier de Veolia

Ce message électronique et ses fichiers attachés sont strictement confidentiels et peuvent contenir des éléments dont Veolia et/ou l'une de ses entités affiliées sont propriétaires. L'utilisation, la divulgation, la publication, la distribution, ou la reproduction non expressément autorisées par Veolia de ce message et de ses pièces attachées sont interdites. Si vous avez reçu ce message par erreur, merci de le retourner immédiatement à son émetteur et de le détruire ainsi que toutes les pièces attachées.

Please consider the environment before printing this email

Veolia Internet Mail Use

The information in this e-mail and any attachment is classified as Veolia and subsidiaries Confidential and Proprietary Information and solely for the attention and use of the named addressee(s). You are hereby notified that any dissemination, distribution or copy of this communication is prohibited without the prior written consent of Veolia and is strictly prohibited by law. If you have received this communication in error, please, notify the sender by reply e-mail.

--------------------------------------------------------------------------------------------

[Vijay Madhavi]

For column I i am using this formula -  = =IFERROR(VLOOKUP(A2,'01/11/23'!$A:$H,8,0))

For column M i am using this formula - =ARRAYFORMULA(IFERROR(VLOOKUP(A2,'01/11/23'!$A:$M,{11,12,13},0)))

Here is the test sheet

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BFpgkM0dER0%2BfemrvwfdsXNujF1Hq83-NpinW4nn9UbTw%40mail.gmail.com.

--

Regards,

Vijay

[Matías Pco.]

Here is a simple code (for understanding purposes) to get all the sheet names, convert them to date format, discard those that are not suitable for conversion to date, validate which previous date is the closest to the current one, once the target date is found, it must be formatted to a string so that it can be embedded in the formula:

function main() {

const ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();

const sName = [];

const nameToDate = [];

const nameToDateOK = [];

for (let a in ss) {

let b = ss[a].getName();

sName.push(b);

// This simple way requires a date structure of type MM/DD/YY. Other structures are possible with certain adjustments.

nameToDate.push(new Date(b));

};

for (let i = 0; i < nameToDate.length; i++) {

const date = new Date(nameToDate[i]);

if (date.toString() !== "Invalid Date") {

nameToDateOK.push(nameToDate[i]);

};

};

const dateClosest = getPreviousDateToCurrentDay(nameToDateOK);

const formattedDate = dateClosest.toLocaleDateString("en-US", { month: "numeric", day: "numeric", year: "numeric", }).toString();

console.log(`"The closest previous date to today is: ${formattedDate}"`);

//From here on, just enter FormattedDate to the formula

};

function getPreviousDateToCurrentDay(dateObj) {

const differences = [];

for (let i = 0; i < dateObj.length; i++) {

differences.push(Math.abs(dateObj[i] - Date.now()));

};

let indexClosestDate = -1;

let minDifference = Infinity;

for (let i = 0; i < differences.length; i++) {

if (differences[i] < minDifference) {

minDifference = differences[i];

indexClosestDate = i;

};

};

return dateObj[indexClosestDate];

}; 

Of course this code can be greatly improved!

[Keith Andersen]

Take it away Matias. Seems like you have great handle on this. 👍👍

Vijay you're in good hands.

Cheers

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c43bc7d1-3872-4fdf-b359-24a6d5d67fe1n%40googlegroups.com.

[Keith Andersen]

Vijay,

I have made a script that will copy your active sheet ( so you will have to open the spreadsheet and click on the sheet you want copied), and rename it today's date and puts the arrayformulas in column I2 and M2 with the previous sheets name as the VLOOKUP range .

Couple things first:

I changed your formula in the I column to an arrayformula. Otherwise you would have had to drag it down manually. 

M column formula was already am arrayformula. However, you have it pulling in 3 columns {11,12,13}. Do you mean to do that as it will overwrite data in columns N and O?

Also, does your data in the live sheet start on row 2 or row 3?

What functions do you currently have on your App Script page (editor)?

Matias's script is great but a little complicated. Mine is shorter and simpler. Always many ways to skin a cat😉.

I'll await your response.

[Vijay Madhavi]

Hello Keith,

Script and formula look good to me.

I will do the testing on my main sheet and get back to you with the analysis.

I will also respond to your last email questions either on Monday or even before if possible.

Regards,

Vijay

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BEtkORS4bGCGuR4QVvowfL%3D-dCmtrwxQHR6S4Px%3DfUf%2BQ%40mail.gmail.com.

--

Regards,

Vijay

[Keith Andersen]

If you like I can write script tat will give you a menu at the top to choose functions from to run. Otherwise, you could create a button to run the script.

I'm not sure how much experience you have with writing app script code. 

Anyway, the script given has the clearing of columns N and O blocked out until the questions were answered.

Until next time.

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ-LwLNceoxKa1Eak8g7H7phY2hGUQdekVN6YFhCnvU6CHE5GA%40mail.gmail.com.

[Vijay Madhavi]

I am so thankful and grateful for your kind gesture. I could not expect a better day than this.

I have created a button for your script.

also,

Please find my response to your questions below

M column formula was already am arrayformula. However, you have it pulling in 3 columns {11,12,13}. Do you mean to do that as it will overwrite data in columns N and O?

Yes i want to pull the data from 3 columns.

Also, does your data in the live sheet start on row 2 or row 3?

row 3

What functions do you currently have on your App Script page (editor)?

I have in my main file the below functions which are excluding the one that you created in app script.

Paste value function

I have delete cell function

Vookup function

I fear creating too many functions would hamper my sheet performance. On the other hand, I also need it to ease my daily work.

Please suggest if there is any alternate way to show less impact on my sheet performance.

Regards,

Vijay

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGOS1%3DM1hryveqgZiB7mejXPvL2PqXiq7Aj_2qhpGpsQQ%40mail.gmail.com.

--

Regards,

Vijay

[Keith Andersen]

Vijay,

My pleasure to help. 

Tomorrow I will modify my code to reflect;

1 starting on row 3

2 unblocking the VLOOKUP that pulls in the 3'columns

And to your question, you can have as many functions as you want, they will not affect the performances of your sheet. I don't see any conflict with the many functions running at the same time.

Tomorrow, after I'm done with the code, I will include the special menu that it makes at the top of the sheet and you can use that or use your button. It'll just be an extra thing for you.

Also, it is important that you are on the sheet that you want to copy when you run this script. 

Anyway, I can wrap this all up for you tomorrow.

Cheers

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ-LwLOrSLSD%2BTvn5tr%2BPe4eZZ5wVBiG98Yfk5XGjLrmjGODQA%40mail.gmail.com.

[Vijay Madhavi]

Thanks a lot Keith for looking into this request again🙏

I have also tested your code in the main sheet and it is working as expected😊

I made slight changes to your code to fit my requirements and it is working as well.

I also need two more conditions to be fulfilled in this code.

1st condition

When we execute the code, duplicate sheet from the current sheet which in this case is 02/11/23, the paste value function should apply on this date only not the current sheet which will be

27/11/2024

2nd condition

When we execute the code, The previous date spreadsheet tab should auto hide keeping the current date spreadsheet tab open.

I have attached the code and test sheet for your reference.

Regards,

Vijay

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGK4mWWsfA6X2OZ%3D-GdGi_vAz8tt3r2BA2fx%2BCf41qrfQ%40mail.gmail.com.

--

Regards,

Vijay

[Vijay Madhavi]

--

Regards,

Vijay