Send datepicker to google sheets specific cells

[Eddie_a]

I saw only this video on forms to sheets

What blocks to use to send DatePicker or its label to specific cells like ‘Input!A1’ in google sheets

[TIMAI2]

One method is to use a google apps script bound to the spreadsheet, then to post the data required to the script, which will then apply the data to the selected sheet, and return the value and content.

APPS SCRIPT

//for testing with AI2
function doPost(e) { 
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName(e.parameter.sheet); 
  var id = e.parameter.id ;
  var cell = e.parameter.range;
  sh.getRange(cell).setValue(id);
  return ContentService.createTextOutput(JSON.stringify({"VALUE":id,"CELL":cell}));
}

BLOCKS

SCREEN

SHEET

(Google Sheets kindly recognises the string as a date!)

When ever you edit or change the google apps script, remember:

1 Open the script project
2 Go to Publish
3 Deploy as Web App
4 Project version: - select New from the dropdown
5 Execute the app as: your google account address (email)
6 Who has access to the app: Anyone, even anonymous
7 Press the Update button

You have to do this EVERY TIME you change your script

[Eddie_a]

hi,

1. in publish, im getting app not verified

2. what is textbox 1, 2, 3??? are you labeling your datepicker or something?

[Eddie_a]

Ok i’ve gotten it to publish
I’m getting HTML script back in my return thing im also building, it may be interfering

I think i need to know about the textbox 1 2 3

[TIMAI2]

Textboxes 1,2, and 3 (you can see them on the SCREEN image above), contain the data required by the app to send the correct information to the web app to set the correct cell and data.

Textbox 1 contains the Sheet name
Textbox 2 contains the Cell Range/Reference
Textbox 3 contains the output from the Datepicker

It is written out like this to hopefully make it more obvious as to what is going on and what data is required.

You can always hard code 1 and 2 if they will always be the same, and if the user does not need to see the date that has been picked, that value and the button action can be handled by the After DatePicked event to automate the upload of the data to the google sheet.

[Eddie_a]

ok thankyou
I separated this stuff all to itself. I am getting html code “returned” and its not posting. but I see what your doing and wont bother you more. my first day with code, 2nd day with google sheets, im an excel vba guy.
tnx
eddie

[TIMAI2]

@Eddie_a

I am getting html code “returned”

Sounds like a permissions issue with the script. Open up the script project and run the doPost(e) script there. It should offer to review permissions, ignore the unsafe stuff. The script will fail because you have not fed it parameters.

If that doesn't fix it, follow my "update the project" guide in my earlier post.

[Eddie_a]

ok will do