Flutter to Google Sheets - how can I add the date of submission automatically?

Question

I have an app in Flutter that lets users choose from a series of drop down lists information that will be sent to a Google Sheets spreadsheet - that information populates the spreadsheet, adding a new row each time data is submitted.

Is there a way to include the date when the data was submitted to the relevant row automatically (as shown in this example)?

Apps Script

function doGet(request) {
  var sheet = SpreadsheetApp.openById("1Yj3ab5NvFVejsJ13U2w3vxFC9dlHWbq0joNnrcqXBQU")
  var result = {"status" : "SUCCESS"};

  try{

        // Get all Parameters
        var fieldDataInputter = request.parameter.fieldDataInputter;
        var sectionofWork = request.parameter.sectionofWork;
        var activityCode = request.parameter.activityCode;
        var operativeName = request.parameter.operativeName;
        var operativeNameHours = request.parameter.operativeNameHours;
        var plantName = request.parameter.plantName;
        var plantNameHours = request.parameter.plantNameHours;
        var notifications = request.parameter.notifications;
        var uploadImage = request.parameter.uploadImage;
        var timestamp = Date.now();
        var isoTimestamp = timestamp.toISOString(); // yyyy-MM-ddTHH:mm:ss.mmmuuuZ

        // Append data on Google Sheet
        var rowData = sheet.appendRow([fieldDataInputter, sectionofWork, activityCode, operativeName, operativeNameHours, plantName, plantNameHours, notifications, uploadImage, timestamp]);  

    }catch(exc){

      result = {"status" : "FAILED", "message": exc };

    }
    return ContentService
      .createTextOutput(JSON.stringify(result))
      .setMimeType(ContentService.MimeType.JSON);
}

The line var tomorrowTimestamp = now.add(new Duration(days: 1)).toIso8601String(); presents the syntax error "missing ) after argument list" - removing that line removes the error, but I'm guessing that's a wrong move.

home.dart (addition of timestamp)

  var fieldDataInputter = ['', ''];
  var operativeName = ['', '', '', '', ''];
  var operativeNameHours = ['', '', '', '', '', '', ''];
  var plantNameHours = ['', '', '', ''];
  var sectionofWork = ['', '', ''];
  var activityCode = ['', '', '', ''];
  var notifications = ['', '', '', ''];
  var uploadImage = ['', '', '', ''];
  var timestamp = [UNSURE HOW TO INCLUDE];

// TextField Controllers
  TextEditingController fieldDataInputterController = TextEditingController();
  TextEditingController operativeNameController = TextEditingController();
  TextEditingController operativeNameHoursController = TextEditingController();
  TextEditingController plantNameController = TextEditingController();
  TextEditingController plantNameHoursController = TextEditingController();
  TextEditingController sectionofWorkController = TextEditingController();
  TextEditingController activityCodeController = TextEditingController();
  TextEditingController notificationsController = TextEditingController();
  TextEditingController uploadImageController = TextEditingController();
  TextEditingController timestampController = TextEditingController();

// Method to Submit Feedback and save it in Google Sheets
  void _submitForm() {
    // Validate returns true if the form is valid, or false
    // otherwise.
    if (_formKey.currentState.validate()) {
      // If the form is valid, proceed.
      FeedbackForm feedbackForm = FeedbackForm(
        fieldDataInputterController.text,
        operativeNameController.text,
        operativeNameHoursController.text,
        plantNameController.text,
        plantNameHoursController.text,
        sectionofWorkController.text,
        activityCodeController.text,
        notificationsController.text,
        uploadImageController.text,
        timestampController.text,
      );

      FormController formController = FormController((String response) {
        print("Response: $response");
        if (response == FormController.STATUS_SUCCESS) {
          // Feedback is saved succesfully in Google Sheets.
          _showSnackbar("Submitted");
        } else {
          // Error Occurred while saving data in Google Sheets.
          _showSnackbar("Error Occurred!");
        }
      }
      );

      _showSnackbar("Submitting");

      // Submit 'feedbackForm' and save it in Google Sheets.
      formController.submitForm(feedbackForm);
    }
  }

form.dart

/// FeedbackForm is a data class which stores data fields of Feedback.
class FeedbackForm {

  String _fieldDataInputter;
  String _operativeName;
  String _operativeNameHours;
  String _plantName;
  String _plantNameHours;
  String _sectionofWork;
  String _activityCode;
  String _notifications;
  String _uploadImage;
  String _timestamp;

  FeedbackForm(this._fieldDataInputter , this._operativeName , this._operativeNameHours ,
      this._plantName , this._plantNameHours ,
      this._sectionofWork , this._activityCode , this._notifications ,
      this._uploadImage , this._timestamp);

  // Method to make GET parameters.
  String toParams() =>
      "?fieldDataInputter=$_fieldDataInputter&operativeName=$_operativeName&operativeNameHours=$_operativeNameHours&plantName=$_plantName&plantNameHours=$_plantNameHours&sectionofWork=$_sectionofWork&activityCode=$_activityCode&notifications=$_notifications&uploadImage=$_uploadImage&timestamp=$_timestamp";
}

home.dart (full)

import 'package:flutter/material.dart';
import '../../controller.dart';
import '../../models/form.dart';
import '../../screens/wrapper.dart';
import '../../services/auth.dart';
import 'package:provider/provider.dart';
import '../../models/user.dart';
import '../../screens/authenticate/requests.dart';
import 'package:intl/intl.dart';

void main() => runApp(MineApp());

class MineApp extends StatelessWidget {
  // This widget is the route of your application.
  @override
  Widget build(BuildContext context) {
    return StreamProvider<User>.value(
      value: AuthService().user,
      child: MaterialApp(
        home: Wrapper(),
      ),
    );
  }
}

void another() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Google Sheet Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(title: 'Flutter Google Sheet Demo'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class AlwaysDisabledFocusNode extends FocusNode {
  @override
  bool get hasFocus => false;
}
class _MyHomePageState extends State<MyHomePage> {



// Create a global key that uniquely identifies the Form widget
// and allows validation of the form.
//
// Note: This is a `GlobalKey<FormState>`,
// not a GlobalKey<MyCustomFormState>.
  final _formKey = GlobalKey<FormState>();
  final _scaffoldKey = GlobalKey<ScaffoldState>();
  final AuthService _auth = AuthService();


  var fieldDataInputter = ['', ''];
  var operativeName = ['', '', '', '', ''];
  var operativeNameHours = ['', '', '', '', '', '', ''];
  var plantNameHours = ['', '', '', ''];
  var sectionofWork = ['', '', ''];
  var activityCode = ['', '', '', ''];
  var notifications = ['', '', '', ''];
  var uploadImage = ['', '', '', ''];

// TextField Controllers
  TextEditingController fieldDataInputterController = TextEditingController();
  TextEditingController operativeNameController = TextEditingController();
  TextEditingController operativeNameHoursController = TextEditingController();
  TextEditingController plantNameController = TextEditingController();
  TextEditingController plantNameHoursController = TextEditingController();
  TextEditingController sectionofWorkController = TextEditingController();
  TextEditingController activityCodeController = TextEditingController();
  TextEditingController notificationsController = TextEditingController();
  TextEditingController uploadImageController = TextEditingController();

// Method to Submit Feedback and save it in Google Sheets
  void _submitForm() {
    // Validate returns true if the form is valid, or false
    // otherwise.
    if (_formKey.currentState.validate()) {
      // If the form is valid, proceed.
      FeedbackForm feedbackForm = FeedbackForm(
        fieldDataInputterController.text,
        operativeNameController.text,
        operativeNameHoursController.text,
        plantNameController.text,
        plantNameHoursController.text,
        sectionofWorkController.text,
        activityCodeController.text,
        notificationsController.text,
        uploadImageController.text,
      );

      FormController formController = FormController((String response) {
        print("Response: $response");
        if (response == FormController.STATUS_SUCCESS) {
          // Feedback is saved succesfully in Google Sheets.
          _showSnackbar("Submitted");
        } else {
          // Error Occurred while saving data in Google Sheets.
          _showSnackbar("Error Occurred!");
        }
      }
      );

      _showSnackbar("Submitting");

      // Submit 'feedbackForm' and save it in Google Sheets.
      formController.submitForm(feedbackForm);
    }
  }

// Method to show snackbar with 'message'.
  _showSnackbar(String message) {
    final snackBar = SnackBar(content: Text(message));
    _scaffoldKey.currentState.showSnackBar(snackBar);
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      backgroundColor: Colors.white,
      key: _scaffoldKey,

      appBar: AppBar(
        title: Text('EPD Site Diary'),
        backgroundColor: Color(0xFF178943),
        elevation: 0.0,
        actions: <Widget>[
          FlatButton.icon(
            icon: Icon(Icons.person, color: Colors.white,),
            label: Text(''),
            onPressed: () async {
              await _auth.signOut();
            },
          ),
          FlatButton.icon(
            icon: Icon(Icons.settings, color: Colors.white,),
            label: Text(''),
            onPressed: () async {
              Navigator.push(
                  context, new MaterialPageRoute(builder: (context) => Home()));
            },
          )
        ],
      ),
      body: Center(
        child: ListView(
          padding: EdgeInsets.fromLTRB(40.0,10.0,40.0,10.0),
          children: <Widget>[
            Form(
                key: _formKey,
                child:
                Padding(padding: EdgeInsets.all(16),
                  child: Column(
                    crossAxisAlignment: CrossAxisAlignment.start,
                    children: <Widget>[

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: fieldDataInputterController,
                        decoration: InputDecoration(
                          labelText: 'Field Data Inputter',
                          hintText: 'Field Data Inputter',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              fieldDataInputterController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return fieldDataInputter
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 20.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: operativeNameController,
                        decoration: InputDecoration(
                          labelText: 'Operative Name',
                          hintText: 'Operative Name',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              operativeNameController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return operativeName
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 5.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: operativeNameHoursController,
                        decoration: InputDecoration(
                          labelText: 'Hours spent on activity',
                          hintText: 'Hours spent on activity',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              operativeNameHoursController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return operativeNameHours
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 20.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: plantNameController,
                        decoration: InputDecoration(
                          labelText: 'Plant Name',
                          hintText: 'Plant Name',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              plantNameController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return plantName
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 5.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: plantNameHoursController,
                        decoration: InputDecoration(
                          labelText: 'Hours spent on activity',
                          hintText: 'Hours spent on activity',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              plantNameHoursController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return plantNameHours
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 20.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: sectionofWorkController,
                        decoration: InputDecoration(
                          labelText: 'Section of Work',
                          hintText: 'Section of Work',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              sectionofWorkController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return sectionofWork
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 20.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: activityCodeController,
                        decoration: InputDecoration(
                          labelText: 'Activity Code',
                          hintText: 'Activity Code',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              activityCodeController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return activityCode
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 20.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: notificationsController,
                        decoration: InputDecoration(
                          labelText: 'Notifications',
                          hintText: 'Notifications',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              notificationsController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return notifications
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                      SizedBox(height: 20.0),

                      TextField(
                        enableInteractiveSelection: false, // will disable paste operation
                        focusNode: new AlwaysDisabledFocusNode(),
                        controller: uploadImageController,
                        decoration: InputDecoration(
                          labelText: 'Upload Image',
                          hintText: 'Upload Image',
                          enabledBorder: OutlineInputBorder(
                            borderSide: BorderSide(color: Colors.grey, width: 1.0),
                          ),
                          suffixIcon: PopupMenuButton<String>(
                            icon: const Icon(Icons.arrow_drop_down),
                            onSelected: (String value) {
                              uploadImageController.text = value;
                            },
                            itemBuilder: (BuildContext context) {
                              return uploadImage
                                  .map<PopupMenuItem<String>>((String value) {
                                return new PopupMenuItem(
                                    child: new Text(value), value: value);
                              }).toList();
                            },
                          ),
                        ),
                      ),

                    ],
                  ),
                )
            ),
            FlatButton(
              color: Color(0xFF178943),
              child: Text(
                'Submit',
                style: TextStyle(
                  color: Colors.white,
                  fontSize: 24.0,
                  fontFamily: 'Lato',
                  letterSpacing: 1,
                ),
              ),
              padding: EdgeInsets.all(10.0,),
              shape: RoundedRectangleBorder(
                borderRadius: BorderRadius.circular(50.0),
              ),
              onPressed:_submitForm,
            ),

            SizedBox(height: 20.0),

          ],
        ),
      ),
    );
  }
}

Solution

I understand that you want an additional cell that displays a timestamp when the user selects a value from the dropdown. In that case you can use in your script a DateTime object like this one:

var timestamp = new DateTime.now();
var isoTimestamp = timestamp.toIso8601String(); // yyyy-MM-ddTHH:mm:ss.mmmuuuZ
var tomorrowTimestamp = timestamp.add(new Duration(days: 1)).toIso8601String();

Alternatively you could set up an installable trigger with Apps Script on your Sheet. Please, don't hesitate to ask me any question to help you further.

---

UPDATE

The code updated on the question returned that error because my code was written on Dart, not JavaScript. If you want to manage the variables in your JavaScript file you can use these alternatives:

var timestamp = Date.now();
var isoTimestamp = timestamp.toISOString(); // yyyy-MM-ddTHH:mm:ss.mmmuuuZ

Those examples use the Date object from JavaScript. Please, feel free to ask more questions.

Comments

[Niall Devlin]

Is it possible to do this?

[Jacques-Guzel Heron]

Hi there @NiallDevlin! To further help you I'd need to ask you for sharing the source code.

[Niall Devlin]

Thanks @Jacques-GuzelHeron, I've added the code to the main post.

[Niall Devlin]

Thanks so much Jacques, I really appreciate it! I had added those lines of code to my current apps script (seen in original post), but I guess I'm going wrong somewhere. Do I need to add to my .dart file too?

[Jacques-Guzel Heron]

@NiallDevlin, I just updated my answer. Please, check it out to see if it helps you.

[Niall Devlin]

Thanks again @Jacques-Guzel Heron. I added that to my Apps Script code, but I can't get it to appear when submit is tapped on the app. I've added updated to my original post (namely Apps Script, home.dart (addition of timestamp) and form.dart), can you tell me where I'm going wrong? I use TextFormField to call the other fields.

[Jacques-Guzel Heron]

@NiallDevlin, I reviewed your code and it isn't clear for me where it's wrong. To confine the issue, please add some logs on the important steps to see where the script fails.

[Niall Devlin]

The script isn't failing (I receive no errors), but the date isn't appearing automatically in Google Sheets per submission via the app as intended. I can get the date to appear on the spreadsheet if I enter content into a cell directly using that Apps Script, but not when it's via the app.

[Jacques-Guzel Heron]

@NiallDevlin, please update the _submitForm() to include an appropriate TextField() function for timestamp management, and please declare the var timestamp as an array of empty strings too.

[Niall Devlin]

In what way do I need to update it? At the moment I'm using TextEditingController timestampController = TextEditingController(); and timestampController.text,. In the meantime I have 3 dropdowns (day, month and year) where users must selected the correct date, it then appears on the spreadsheet - it's slower and less user friendly than just having the date appear automatically. I also added var formatteddate = [DateTime.now()]; and String _formatteddate = new DateFormat.yMMMMd().format(_currentdate); so that the date appears in the app (though not in Google Sheets).