How to get a date from sheet formated exactly as written and as a string.

[Thomas Jönsson]

Hi.

Trying to fetch a date in format as "2020-01-01" from sheet for use inside a script.

I want it to be return it as a string as it is: "yyyy-mm-dd" so I can use it in 

forDateRange(yyyy-mm-dd ,yyyy-mm-dd ).

When I try different formats in google sheets (date, plain text, numbers e.tc.) I can't get it working. Below are some of the results form different formats. The "text" format should work one think. It is formated correctly ("2020-01-01") but it's length is only 1 so something is wrong.

[ [ Fri Jan 03 2020 15:00:00 GMT-0800 (GMT-08:00) ] ]
[ [ 20200106 ] ]

NaN

NaN
NaN
2020-01-01 <--- This is wrong since it length is considered to be 1, it should be 10.

Thanks in advance.

[Martijn Kraan]

Hi Thomas,

Disclaimer: I'm not from the official Google Ads Scripts team, but maybe I can help with your question.

If you format a value as a date in a Google Sheet and read it with a Google Ads Script, it will return a Date Object.
See this article to read more about Date Objects: https://www.w3schools.com/jsref/jsref_obj_date.asp

You then have several options, but I would then use the Methods & Properties of the Date Object to combine the output you want. See the example script below:

------

function main() {
 
  const SPREADSHEET_URL = 'https://docs.google.com/YOUR-SPREADSHEET-URL-HERE'
  const SHEET_NAME = 'Sheet1';

  const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = ss.getSheetByName(SHEET_NAME);

  const value = sheet.getRange('A1').getValue();
  console.log(value);
  console.log(typeof(value));
 
  console.log(value.getDate());
  console.log(value.getMonth());  
  console.log(value.getFullYear());
 
}

--------

The output of the script looks like this:

Note, that the day & month are off by 1. This is because counting starts at zero, so January is month 0.

Tip: this article contains a lot of great info about working with dates and times in Google Ads scripts: https://developers.google.com/google-ads/scripts/docs/features/dates#spreadsheets

It even contains a small chapter about Google Sheets

I hope this helps. Good luck!

Gr, Martijn

[Google Ads Scripter]

Hello and thank you very much.

Yeah, I recon i just have to "give up" trying to fetch the value as a clean string.

Using Utilities.formatDate(value,timeZone,"yyyy-MM-dd"); seems like a cleaner way to go.

function main() {
 
  const SPREADSHEET_URL = 'https://docs.google.com/YOUR-SPREADSHEET-URL-HERE'
  const SHEET_NAME = 'Sheet1';

  const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = ss.getSheetByName(SHEET_NAME);
 
  const value = sheet.getRange('A1').getValue();
  console.log(value);
  console.log(typeof(value));

  var timeZone = AdWordsApp.currentAccount().getTimeZone();
  var myDate = Utilities.formatDate(value,timeZone,"yyyy-MM-dd");
  console.log(myDate);
}

Output:
Mon Oct 19 2015 15:00:00 GMT-0700 (GMT-07:00)
object
2015-10-20

[Google Ads Scripts Forum Advisor]

Hello,

I’m James from the Google Ads scripts support team. Thank you for reaching out to us.

I confirm that using Utilities.formatDate() allows you to properly format the retrieved date from your spreadsheet. You may refer here for more information. However, if I misunderstood your concern, kindly provide more details with your concern so that we can accurately address your query.

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2f4Il3:ref

[MAHADI HASAN]

🌼🌼🌼🌼🌼

Vào Th 3, 11 thg 10, 2022 vào lúc 12:02 Google Ads Scripts Forum on behalf of adsscripts <adwords...@googlegroups.com> đã viết:

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/xsoTo000000000000000000000000000000000000000000000RJKONN00Vy_0vVF6SHOnwBtwhhnnaQ%40sfdc.net.

[Google Ads Scripter]

Thanks, all set then. Have a great day! :-)