小贝子编程

飘然到 Google 表格 - 如何自动添加提交日期?



我在 Flutter 中有一个应用程序，允许用户从一系列下拉列表中进行选择，这些信息将发送到 Google 表格电子表格 - 该信息填充电子表格，每次提交数据时都会添加新行。

有没有办法包含数据自动提交到相关行的日期(如本例所示(？

应用脚本 

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);
}

该行var tomorrowTimestamp = now.add(new Duration(days: 1)).toIso8601String();在参数列表后显示语法错误"丢失"(-删除该行会删除错误，但我猜这是一个错误的举动。

home.dart(添加时间戳(

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);
}
}

表单.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";
}

首页飞镖(已满(

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),
],
),
),
);
}
}

我知道您需要一个额外的单元格，当用户从下拉列表中选择值时显示时间戳。在这种情况下，您可以在脚本中使用如下所示的DateTime对象：

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

或者，您可以使用工作表上的应用程序脚本设置可安装触发器。请不要犹豫，问我任何问题，以进一步帮助您。

更新

在问题上更新的代码返回了该错误，因为我的代码是用 Dart 而不是 JavaScript 编写的。如果你想管理JavaScript文件中的变量，你可以使用以下替代方法：

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

这些示例使用 JavaScript 中的 Date 对象。请随时提出更多问题。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium